Question

Problem 1: You do not need Azure SQL for this problem Do the following. a) Write a description for the attached ER diagram. b) Convert the attached ER diagram to a Relational Database. c) Draw a Schema Diagram for the relational database. Paseenger Empbyee Name Firet Name Last Name @nal Address》 Fiet Nam Trains La3t Name Hire Dsie Service Years Cate Ride Licenoe d Licence Cis88 Licenoe. Expiraton City 2IP Taxi Cotfcation Date Date Parking Spot arks Overnigh Spot Number Has

Answer 1

a) This ER diagram describes the relationship between Passenger, taxi, driver, parking_complex , mechanic and Employee.
i.e basically it describes a database of any cab provider, cab provider have a relationship with employee, driver,
passenger, parking_complex and mechanic. So we can say it is all about Transport system.

Employee have Unary relationship(trainee, trainer) and class Hierarchi with Employee (Driver, Mechanic).

answer b & c>

create table passenger(
passenger_id varchar(30),
first_name varchar(30),
last_name varchar(30),
email_add varchar(30),
primary key(passenger_id));

create table ride(
ride_id varchar(30),
ride_date date,
driver_id varchar(30),
taxi_id varchar(30),
primary key(ride_id));

create table taxi(
taxi_id varchar(30),
make varchar(20),
model varchar(20),
year varchar(4),
primary key(taxi_id));

create table parking_spot(
spot_number int,
level int,
parking_complex_id varchar(30),
primary key(spot_number, parking_complex_id));

create table parking_complex(
parking_complex_id varchar(30),
name varchar(30),
loc_numer varchar(30),
loc_street varchar(30),
loc_city varchar(30),
loc_state varchar(30),
loc_zip varchar(30),
primary key(parking_complex_id));

create table parks_overnight(
spot_number int,
parking_complex_id varchar(30),
taxi_id varchar(30),
primary key(parking_complex_id, taxi_id));

create table employee(
ssn varchar(30),
firstname varchar(30),
lastname varchar(30),
hire_date date,
service_years int,
trainer_id varchar(30),
primary key(ssn));

create table driver(
ssn varchar(30),
licence_id varchar(30),
licence_class varchar(30),
licence_expiration varchar(4),
primary key(ssn));

create table mechanic(
ssn varchar(30),
con_type varchar(30),
con_date date,
primary key(ssn));

create table services(
ssn varchar(30),
service_id varchar(30),
taxi_id varchar(30),
service_date date,
primary key(ssn, service_id));