Question

Assume that Garden Glory designs a database with the following tables: OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType) OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID) GG_SERVICE (ServiceID, ServiceDescription, CostPerHour); EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel) PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked) The referential integrity constraints are: OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_ PROPERTY ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE Assume that OwnerID in OWNER, PropertyID in PROPERTY, ServiceID in GG_SERVICE, EmployeeID in EMPLOYEE, and PropertyServiceID in PROPERTY_SERVICE are surrogate keys with values as follows: OwnerID Start at 1 Increment by 1 PropertyID Start at 1 Increment by 1 ServiceID Start at 1 Increment by 1 EmployeeID Start at 1 Increment by 1 PropertyServiceID Start at 1 Increment by 1

How to create the table

Answer 1

create table OWNER (OwnerID int Primary Key Auto_increment, OwnerName varchar(20), OwnerEmailAddress varchar(50), OwnerType varchar(4));

create table OWNED_PROPERTY (PropertyID int Primary Key Auto_increment, PropertyName varchar(20), PropertyType varchar(4), Street varchar(50), City varchar(20), State varchar(30), Zip int, OwnerID int references OWNER(OwnerId));

create table GG_SERVICE (ServiceID int Primary Key Auto_increment, ServiceDescription varchar(100), CostPerHour int);

create table EMPLOYEE (EmployeeID int Primary Key Auto_increment , LastName varchar(20), FirstName varchar(20), CellPhone int(10), ExperienceLevel varchar(30));

create table PROPERTY_SERVICE (PropertyServiceID int Auto_increment, PropertyID int references OWNED_PROPERTY(PropertyID), ServiceID references GG_SERVICE(ServiceID), ServiceDate Date, EmployeeID int references EMPLOYEE(EmployeeID), HoursWorked int);