Question

In: Computer Science

Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER...

Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables:

OWNER (OwnerID, OwnerName, OwnerEmail, 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, and EmployeeID in EMPLOYEE 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

Sample data are shown in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Unknown, Junior, Senior, Master or SuperMaster. These tables, referential integrity constraints, and data are used

Sample Data for Garden Glory OWNER Table OwnerID OwnerName OwnerEmailAddress OwnerType

1 Mary Jones [email protected] Individual

2 DT Enterprises [email protected] Corporation

3 Sam Douglas [email protected] Individual

4 UNY Enterprises [email protected] Corporation

5 Doug Samuels [email protected] Individual

Sample Data for Garden Glory OWNED_PROPERTY Table PropertyID PropertyName PropertyType Street City State ZIP OwnerID

1 Eastlake Building Office 123 Eastlake Seattle WA 98119 2

2 Elm St Apts Apartments 4 East Elm Lynwood WA 98223 1

3 Jefferson Hill Office 42 West 7th St Bellevue WA 98007 2

4 Lake View Apts Apartments 1265 32nd Avenue Redmond WA 98052 3

5 Kodak Heights Apts Apartments 65 32nd Avenue Redmond WA 98052 4

6 Jones House Private Residence 1456 48th St Bellevue WA 98007 1

7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3

8 Samuels House Private Residence 567 151st St Redmond WA 98052 5

as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY. Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data. Write SQL statements and answer questions for this database as follows:

Will you please explain and write the instructions how to write on mySQL and thank you

FIGURE 3-40

Sample Data for Garden Glory EMPLOYEE Table EmployeeID LastName FirstName CellPhone ExperienceLevel

1 Smith Sam 206-254-1234 Master

2 Evanston John 206-254-2345 Senior

3 Murray Dale 206-254-3456 Junior

4 Murphy Jerry 585-545-8765 Master

5 Fontaine Joan 206-254-4567 Senior

FIGURE 3-41

Sample Data for Garden Glory GG_SERVICE Table ServiceID ServiceDescription CostPerHour

1 Mow Lawn 25.00

2 Plant Annuals 25.00

3 Weed Garden 30.00

4 Trim Hedge 45.00

5 Prune Small Tree 60.00

6 Trim Medium Tree 100.00

7 Trim Large Tree 125.00

FIGURE 3-42

Sample Data for Garden Glory PROPERTY_SERVICE Table PropertyServiceID PropertyID ServiceID ServiceDate EmployeeID HoursWorked

1 1 2 2019-05-05 1 4.50

2 3 2 2019-05-08 3 4.50

3 2 1 2019-05-08 2 2.75

4 6 1 2019-05-10 5 2.50

5 5 4 2019-05-12 4 7.50

6 8 1 2019-05-15 4 2.75

7 4 4 2019-05-19 1 1.00

8 7 1 2019-05-21 2 2.50

9 6 3 2019-06-03 5 2.50

10 5 7 2019-06-08 4 10.50

11 8 3 2019-06-12 4 2.75

12 4 5 2019-06-15 1 5.00

13 7 3 2019-06-19 2 4.00

B. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading updates and deletions and justify those assumptions. (Hint: You can combine the SQL for your answers to parts A and B.)

C. Write SQL statements to insert the data into each of the five Garden Glory database tables. Assume that any surrogate key value will be supplied by the DBMS. Use the data in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42.

will you please explain thank you

Solutions

Expert Solution

-- Creating owner table and adding primary key and auto increment constraints

CREATE TABLE `owner` (
  `OwnerID` int(11) NOT NULL,
  `OwnerName` varchar(500) NOT NULL,
  `OwnerEmail` varchar(200) NOT NULL,
  `OwnerType` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `owner`
  ADD PRIMARY KEY (`OwnerID`);

  ALTER TABLE `owner`
  MODIFY `OwnerID` int(11) NOT NULL AUTO_INCREMENT;



-- Creating owned_property table and adding primary key, foriegn key and auto increment constraints

CREATE TABLE `owned_property` (
  `Property_ID` int(11) NOT NULL,
  `Property_Name` varchar(500) NOT NULL,
  `Property_Type` varchar(500) NOT NULL,
  `Street` varchar(500) NOT NULL,
  `City` varchar(500) NOT NULL,
  `State` varchar(500) NOT NULL,
  `Zip` int(11) NOT NULL,
  `OwnerID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `owned_property`
  ADD PRIMARY KEY (`Property_ID`),
  ADD KEY `OwnerID` (`OwnerID`);

  ALTER TABLE `owned_property`
  MODIFY `Property_ID` int(11) NOT NULL AUTO_INCREMENT;

  ALTER TABLE `owned_property`
  ADD CONSTRAINT `owned_property_ibfk_1` FOREIGN KEY (`OwnerID`) REFERENCES `owner` (`OwnerID`);



-- Creating gg_service table and adding primary key and auto increment constraints

  CREATE TABLE `gg_service` (
  `ServiceID` int(11) NOT NULL,
  `ServiceDescription` varchar(500) NOT NULL,
  `CostPerHour` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `gg_service`
  ADD PRIMARY KEY (`ServiceID`);

  ALTER TABLE `gg_service`
  MODIFY `ServiceID` int(11) NOT NULL AUTO_INCREMENT;



-- Creating employee table and adding primary key and auto increment constraints

  CREATE TABLE `employee` (
  `EmployeeID` int(11) NOT NULL,
  `LastName` varchar(100) NOT NULL,
  `FirstName` varchar(100) NOT NULL,
  `CellPhone` varchar(100) NOT NULL,
  `ExperienceLevel` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `employee`
  ADD PRIMARY KEY (`EmployeeID`);

  ALTER TABLE `employee`
  MODIFY `EmployeeID` int(11) NOT NULL AUTO_INCREMENT;



-- Creating property_service table and adding primary key, foriegn keys and auto increment constraints

  CREATE TABLE `property_service` (
  `PropertyServiceID` int(11) NOT NULL,
  `PropertyID` int(11) NOT NULL,
  `ServiceID` int(11) NOT NULL,
  `ServiceDate` date NOT NULL,
  `EmployeeID` int(11) NOT NULL,
  `HoursWorked` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  ALTER TABLE `property_service`
  ADD PRIMARY KEY (`PropertyServiceID`),
  ADD KEY `PropertyID` (`PropertyID`),
  ADD KEY `ServiceID` (`ServiceID`),
  ADD KEY `EmployeeID` (`EmployeeID`);

  ALTER TABLE `property_service`
  MODIFY `PropertyServiceID` int(11) NOT NULL AUTO_INCREMENT;

  ALTER TABLE `property_service`
  ADD CONSTRAINT `property_service_ibfk_1` FOREIGN KEY (`PropertyID`) REFERENCES `owned_property` (`Property_ID`),
  ADD CONSTRAINT `property_service_ibfk_2` FOREIGN KEY (`ServiceID`) REFERENCES `gg_service` (`ServiceID`),
  ADD CONSTRAINT `property_service_ibfk_3` FOREIGN KEY (`EmployeeID`) REFERENCES `employee` (`EmployeeID`);








-- Inserting given values into employee table

insert into employee(LastName,FirstName,CellPhone,ExperienceLevel) values('Smith','Sam','206-254-1234','Master');
insert into employee(LastName,FirstName,CellPhone,ExperienceLevel) values('Evanston','John','206-254-2345','Senior');
insert into employee(LastName,FirstName,CellPhone,ExperienceLevel) values('Murray','Dale','206-254-3456','Junior');
insert into employee(LastName,FirstName,CellPhone,ExperienceLevel) values('Murphy','Jerry','585-545-87865','Master');
insert into employee(LastName,FirstName,CellPhone,ExperienceLevel) values('Fontain','Joan','206-254-4567','Senior');


-- Inserting given values into gg_service table

insert into gg_service(ServiceDescription,CostPerHour) values('Mow Lawn','25.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Plant Annuals','25.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Weed Garden','30.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Trim Hedge','45.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Prune Small Tree','60.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Trim Medium Tree','100.00');
insert into gg_service(ServiceDescription,CostPerHour) values('Trim Large Tree','125.00');


-- Inserting given values into owner table

insert into owner(OwnerName,OwnerEmail,OwnerType) values('Mary Jones','[email protected]','Individual');
insert into owner(OwnerName,OwnerEmail,OwnerType) values('DT Enterprises','[email protected]','Corporation');
insert into owner(OwnerName,OwnerEmail,OwnerType) values('Sam Douglas','[email protected]','Individual');
insert into owner(OwnerName,OwnerEmail,OwnerType) values('UNY Enterprises','[email protected]','Corporation');
insert into owner(OwnerName,OwnerEmail,OwnerType) values('Doug Samuels','[email protected]','Individual');


-- Inserting given values into owned_property table

insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Eastlake Building','Office','123','Eastlake','Seattle WA','98119',2);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Elm St Apts', 'Apartments', '4', 'East Elm', 'Lynwood WA', '98223', 1);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Jefferson Hill', 'Office', '42', 'West 7th St', 'Bellevue WA', '98007', 2);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Lake View Apts', 'Apartments', '1265', '32nd Avenue', 'Redmond WA', '98052', 3);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Kodak Heights Apts', 'Apartments', '65', '32nd Avenue', 'Redmond WA', '98052', 4);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Jones House', 'Private Residence', '1456', '48th St', 'Bellevue WA', '98007', 1);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Douglas House', 'Private Residence', '1567', '51st St', 'Bellevue WA', '98007', 3);
insert into owned_property(Property_Name,Property_Type,Street,City,State,Zip,OwnerID) values('Samuels House', 'Private Residence', '567', '151st St', 'Redmond WA', '98052', 5);


-- Inserting given values into property_Service table

insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('1', '2', '2019-05-05', '1', '4.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('3', '2', '2019-05-08', '3', '4.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('2', '1', '2019-05-08', '2', '2.75');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('6', '1', '2019-05-10', '5', '2.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('5', '4', '2019-05-12', '4', '7.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('8', '1', '2019-05-15', '4', '2.75');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('4', '4', '2019-05-19', '1', '1.00');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('7', '1', '2019-05-21', '2', '2.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('6', '3', '2019-06-03', '5', '2.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('5', '7', '2019-06-08', '4', '10.50');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('8', '3', '2019-06-12', '4', '2.75');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('4', '5', '2019-06-15', '1', '5.00');
insert into property_service(PropertyID,ServiceID,ServiceDate,EmployeeID,HoursWorked) values('7', '3', '2019-06-19', '2', '4.00');

















I have created all the tables and also wrote queries to insert all the data into the tables. You can directly create and import above sql file into mysql databse.

Thank you.


Related Solutions

Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER...
Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER (OwnerID, OwnerName, OwnerEmail, 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...
Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER...
Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER (OwnerID, OwnerName, OwnerEmail, 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...
Edit question Garden Glory Project Questions Assume that Garden Glory designs a database with the following...
Edit question Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables: OWNER (OwnerID, OwnerName, OwnerEmail, 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...
The following tables form part of a database (Flights Database) held in a relational DBMS: employee...
The following tables form part of a database (Flights Database) held in a relational DBMS: employee (empNo, empName, empSalary, empPosition) aircraft (aircraftNo, acName, acModel, acFlyingRange) flight (flightNo, aircraftNo, fromAirport, toAirport, flightDistance, departTime, arriveTime) certified (empNo, aircraftNo) Where:  employee contains details of all employees (pilots and non-pilots) and empNo is the primary key;  aircraft contains details of aircraft and C is the primary key.  flight contains details of flights and (flightNo, aircraftNo) form the primary key.  certified...
QUESTION: The following tables describe the content of a relational database: a) Identify and classify the...
QUESTION: The following tables describe the content of a relational database: a) Identify and classify the tables as either entity or relationship The first step in building an E-R model is to identify the entities. Having identified the entities, the next step is to identify all the relationships that exist between these entities. Using the content of the relational database above: b) Using the relations in the relational database, explain how one can transform relationship in E-R model into a...
Question 2. The following tables provide some example data that will be kept in the database....
Question 2. The following tables provide some example data that will be kept in the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1. Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands.. Table: actor act_id | act_fname | act_lname | act_gender 101 | James | Stewart | M 102 | Deborah | Kerr | F 103 | Peter |...
Please use an Access database with two tables to answer the following: use an example to...
Please use an Access database with two tables to answer the following: use an example to discuss the difference between a right, left, and inner join. Next, perform the left joint, right joint, and inner joint all on the each of the two tables.
Garden Plot Calculator Assume that you have a garden plot like the following: The blue circle...
Garden Plot Calculator Assume that you have a garden plot like the following: The blue circle represents a fountain and the orange part the flower bed. Remember that the area of a square is the length of oneof its sides squared. Also, the area of a circle is πr, where r is the radius of the circle. The volume is the square footage times thedepth.For this project, you get to write a Python script to perform some calculations for the...
Database Management System Complete the following exercises in Connolly & Begg: 4.8 The following tables from...
Database Management System Complete the following exercises in Connolly & Begg: 4.8 The following tables from part of a database held in a relational DBMS Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) a) Identify the foreign keys in this schema. b) Explain how the entity integrity rule and the referential integrity rule apply to these relations. 5.8 Describe the relations that would be produced by the following relational...
Please create the following tables for a tool rental database with appropriate primary keys & foreign...
Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. Assumptions: 1. Each tool belongs to a category. 2. Each category may have a parent category but the parent category should not have a parent category (so at most two levels). E.g., a Tool A belongs to the electric mower, and electric mowers belong to mowers. Mower has no parent category. 3. Each tool can be rented at different time units. The typical...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT