Create 2 separate databases from the 2 ER diagrams below. All data needs to be i
ID: 3808943 • Letter: C
Question
Create 2 separate databases from the 2 ER diagrams below. All data needs to be included to create the databases and tables to include foreign and primary key contraints. The title of each problem will be the database name. In addition, write at least 3 insert statements for each DB with any info. Please do not take any shortcuts as the DB info needs to be very specific. Thank you.
Problem 2: Rock Band Last Name Nickname Type First Name Brand Name SSN Band Members (1,1 0,N) Play Instrument (1,N) Has Role Problem 3: Dog Walkers State Street Zipcode Address Last Name Phone Number Name Type First Name Customer ID (1,1) 1,3 Customers H Own Dog (1,1) Dog Walkers Walk Time Employee ID NameExplanation / Answer
Problem 2: Rock Band
Create table BandMembers
(
SSN varchar(20) not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,
Nickname varchar(30),
Instrument_ID integer,
Primary Key(SSN),
Foreign Key(Instrument_ID) References Instrument(ID)
);
Create table Instrument
(
ID integer not null,
BrandName varchar(30) not null,
Type not null,
Primary key(ID)
);
Create table Role
(
MemberSSN varchar(20) not null,
RoleName varchar(20) not null,
Primary Key(MemberSSN,RoleName),
Foreign Key(MemberSSN) References BandMembers(SSN)
);
Problem 3: Dog Walkers
Create table Customers
(
Customer_ID integer not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,
PhoneNumber varchar(20) not null,
Street varchar(30) not null,
City varchar(20) not null,
State varchar(20) not null,
ZipCode integer not null,
Primary Key(Customer_ID)
);
Create table Dog
(
Customer_ID integer not null,
Name varchar(30) not null,
Type varchar(30),
Primary Key(Customer_ID,Name),
Foreign Key(Customer_ID) References Customers(Customer_ID),
Foreign Key(Employee_ID) References DogWalkers(Employee_ID)
);
Create table DogWalkers
(
Employee_ID integer not null,
name varchar(30) not null,
Primary Key(Employee_ID)
);
Create table WalkTime
(
Employee_ID integer not null,
Customer_ID integer not null,
DogName varchar(30) not null,
WalkTime Time not null,
Primary Key (Employee_ID,Customer_ID,DogName)
);