IT 240 Name:___ZihanWang________
HW 2 (Week 3–4 Activities)
Worth 20 points
Instructions:
1. Download
and
save this document
2. Place your full name on it.
3. Type your answers dirctly under each question, Leaving the questions. Do Not delete the questions.
4. Submit this document and your Access DB
Part I Garden Glory Project
A. Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations. Garden Glory is owned by two partners. They employ two office administrators and a number of full- and part-time gardeners. Garden Glory will provide one-time garden services, but it specializes in ongoing service and maintenance. Many of its customers have multiple buildings, apartments, and rental houses that require gardening and lawn maintenance services.
For the following questions, use this data sample:
A. Using these data, state assumptions about functional dependencies among the columns of data. Justify your assumptions on the basis of these sample data and also on the basis of what you know about service businesses.
Every City has certian Zips assigned to it. The Zip determines in which city that particular location is located. From data, we can see Settle has 98119 zip, Lyrewood has 98223 and Belluve has 98040 zip.
Property Name-> Office
Property names ending with Apts corresponds to Apartments and the rest correspond to Office
B. Given your assumptions in part A, comment on the appropriateness of the following designs:
1. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
Inappropriate, PropertyName can’t be Primary Key as it is not unique
2. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
Inappropriate, ServiceDate can’t be Primary Key as it is not unique
3. PROPERTY (PropertyName, PropertyType, Street, City, Zip, ServiceDate,
Appropriate as combination of PropertyName and ServiceDate is unique
4. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
Appropriate as PropertyId is unique
5. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate, Description, Amount)
Inappropriate, ServiceDate can’t be Primary Key as it is not unique
6. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate)
and
SERVICE (ServiceDate, Description, Amount)
Inappropriate, ServiceDate can’t be Primary Key as it is not unique
7. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceDate)
and:
SERVICE (ServiceID, ServiceDate, Description, Amount)
Appropriate as both PropertyID and ServiceID are unique
8. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip, ServiceID)
and:
SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)
Inappropriate as ServiceDate can’t be foreign key as not unique
9. PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, Zip)
and:
SERVICE (ServiceID, ServiceDate, Description, Amount, PropertyID)
Inappropriate, ServiceDate can’t be Primary Key as it is not unique and PropertyId can be a foreign key
C. Suppose Garden Glory decides to add the following table:
SERVICE-FEE (PropertyID, ServiceID, Description, Amount)
Add this table to what you consider to be the best design in your answer to part B. Modify the tables from part B as necessary to minimize the amount of data duplication. Will this design work for the data in Figure 2-34? If not, modify the design so that this data will work. State the assumptions implied by this design.
Number 9 will be most apt as both the keys ProperyId and ServiceId are present in SERVICE- FFE.
Design:
PROPERTY(PropertyID, PropertyName, PropertyType, Street,City,Zip) SERVICE(ServiceID, ServiceDate) and SERVICE-FEE(PropertyID, ServiceID,Description, Amount)
We will remoce Description, Amount and PropertyID from the SERVICE table.
The design will work with the above data if we add ProperertyID and ServiceID
Part II. 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 OWNED_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
Use the Sample data are shown in Figures 3-38, 3-39, 3-40, 3-41, and 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Junior, Senior, or Master. These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow.
Run these statements in Access.
Name your database GARDEN_GLORY.
Use data types consistent with Access.
For each SQL statement, write and show the results based on your data.
1. Write CREATE TABLE statements for each of these tables. Omit foreign keys.
Combine A & B.
CREATE TABLE OWNER( OwnerID Int NOT NULL IDENTITY (1,1), OwnerName Char(50) NOT NULL, OwnerEmailAddress Char(50) NULL, OwnerType Char(25) NULL, Constraints OWNER_PK PRIMARY KEY(OwnerID) );
CREATE TABLE OWNED_PROPERTY( PropertyID Int NOT NULL IDENTITY (1,1), PropertyName Char(50) NOT NULL PropertyType Char(50) NOT NULL, Street Char(50) NOT NULL, City Char(50) NOT NULL, State Char(2) NOT NULL, Zip Char(10) NOT NULL, OwnerID Int NOT NULL, Constraint OWNED_PROPERTY_PK PRIMARY KEY(PropertyID),);
CREATE TABLE EMPLOYEE( EmployeeID Int NOT NULL IDENTITY (1,1), LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, CellPhone Char(10) NOT NULL, ExperienceLevel Char(25) NOT NULL, Constraint EMPLOYEE_PK PRIMARY KEY(EmployeeID));
CREATE TABLE GG_SERVICE( ServiceID Int NOT NULL IDENTITY (1,1) ServiceDescription VarChar(25) NOT NULL, CostPerHour Numeric(6, 2) NULL, Constraint GG_SERIVCE_PK PRIMARY KEY(ServiceID));
CREATE TABLE PROPERTY_SERVICE( PropertyServiceID Int NOT NULL IDENTITY(1, 1) PropertyID Int NOT NULL; ServiceID Int NOT NULL, ServiceDate Char(10) NOT NULL, EmployeeID Int NOT NULL, HoursWorked Numeric(4,2) NULL, Constraint PROP_SERVICE_PK PRIMARY KEY (PropertyServiceID)
A. 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 questions A and B)
ALTER TABLE PROPERTY ADD CONSTRAINT PROP_OWNER_OWNERID_FK FOREIGN KEY (OwnerID) REFERENCES OWNER(OwnerID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE SERICCE ADD CONSTRAINTPROP_SERVICE_PROPERTYID_FK FOREIGN KEY(PropertyID) REFERENCES PROPERTY (PropertyID) ON DELETE NOACTION ON UPDATE NO ACTION;
ALTER TABLE SERVICE ADD CONSTRAINTPROP_SERVICE_EMPLOYEEID_FK FOREIGN KEY(EmployeeID) REFERENCES EMPLOYEE (EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION;
B. 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 Figures 3-38, 3-39, 3-40, 3-41, and 3-42 on pages 257–258.
FROM OWNER
INSERT INTO OWNER VALUES (‘Mary Jones’,’Mary.Jones@somewhere.com’,’Individual’); INSERT INTO OWNER VALUES (‘DT Enterprises’, ‘DTE@dte.com’,’ Corporation’); INSERT INTO OWNER VALUES (‘Sam Douglas,’Sam.Douglas@somewhere.com’,’Individual’); INSERT INTO OWNER VALUES (‘UNY Enterprises,’UNYE@unye.com’,’Corporation’); INSERT INTO OWNER VALUES (‘Doug Samuels,’Doug.Samuels@somewhere.com’,’Individual’);
FROM OWNED_PROPERTY
INSERT INTO OWNED_PROPERTY VALUES (‘Eastlake Building’,’Office’,’123 Eastlake’,’Seattle’,’WA’,’98119’,’2’); INSERT INTO OWNED_PROPERTY VALUES(‘Elm St Apts’, ‘Apartments’, ‘4 East Elm’, ‘Lynwood’, ‘WA’, ‘98223’, 1); INSERT INTO OWNED_PROPERTY VALUES(‘Jefferson Hill’, ‘Office’, ’42 West 7th St’, ‘Bellevue’, ‘WA’, ‘98007’, 2);
INSERT INTO OWNED_PROPERTY VALUES(‘Lake View Apts’, ‘Apartments’, ‘1265 32nd Avenue’, ‘Redmond’, ‘WA’, ‘98052’, 3);
INSERT INTO OWNED_PROPERTY VALUES(‘Kodak Heights Apts’, ‘Apartments’, ’65 32nd Avenue’, ‘Redmond’, ‘WA’, ‘98052’, 4);
INSERT INTO OWNED_PROPERTY VALUES(‘Jones House’, ‘Private Residence’, ‘1456 48th St’, ‘Bellevue’, ‘WA’, ‘98007’, 1);
INSERT INTO OWNED_PROPERTY VALUES(‘Douglas House’, ‘Private Residence’, ‘1567 51st St’, ‘Bellevue’, ‘WA’, ‘98007’, 3);
INSERT INTO OWNED_PROPERTY VALUES(‘Samuels House’, ‘Private Residence’, ‘567 151st St’, ‘Redmond’, ‘WA’, ‘98052’, 5);
FROM EMPLOYEE
INSERT INTO EMPLOYEE VALUES(‘Smith’, ‘Sam’, ‘206-254-1234’, ‘Master’);
INSERT INTO EMPLOYEE VALUES(‘Evanston’, ‘John’,’206-254-2345′, ‘Senior’);
INSERT INTO EMPLOYEE VALUES(‘Murray’, ‘Dale’, ‘206-254-3456’, ‘Junior’);
INSERT INTO EMPLOYEE VALUES(‘Murphy’, ‘Jerry’, ‘585-545-8765’, ‘Master’);
INSERT INTO EMPLOYEE VALUES(‘Fontaine’, ‘Joan’, ‘206-254-4567’, ‘Senior’);
FROM GG_SERVICE
INSERT INTO GG_SERVICE VALUES(‘Mow Lawn’, 25.00);
INSERT INTO GG_SERVICE VALUES(‘Plant Annuals’, 25.00);
INSERT INTO GG_SERVICE VALUES(‘Weed Garden’, 30.00);
INSERT INTO GG_SERVICE VALUES(‘Trim Hedge’, 45.00);
INSERT INTO GG_SERVICE VALUES(‘Prune Small Tree’, 60.00);
INSERT INTO GG_SERVICE VALUES(‘Trim Medium Tree’,100.00);
INSERT INTO GG_SERVICE VALUES(‘Trim Large Tree’, 125.00);
FROM PROPERTY_SERVICE
INSERT INTO PROPERTY_SERVICE VALUES(1,2,’05-05-17’,1,4.50); INSERT INTO PROPERTY_SERVICE VALUES(3,2,’08-05-17’,3,4.50); INSERT INTO PROPERTY_SERVICE VALUES(2,1,’08-05-17’,2,2.75); INSERT INTO PROPERTY_SERVICE VALUES(6,1,’10-05-17’,5,2.50); INSERT INTO PROPERTY_SERVICE VALUES(5,4,’12-05-17’,4,7.50); INSERT INTO PROPERTY_SERVICE VALUES(8,1,’15-05-17’,4,2.75); INSERT INTO PROPERTY_SERVICE VALUES(4,4,’19-05-17’,1,1.00); INSERT INTO PROPERTY_SERVICE VALUES(7,1,’21-05-17’,2,2.50); INSERT INTO PROPERTY_SERVICE VALUES(6,3,’03-06-17’,5,2.50); INSERT INTO PROPERTY_SERVICE VALUES(5,7,’08-06-17’,4,10.50); INSERT INTO PROPERTY_SERVICE VALUES(8,3,’12-06-17’,4,2.75); INSERT INTO PROPERTY_SERVICE VALUES(4,5,’15-06-17’,1,5.00); INSERT INTO PROPERTY_SERVICE VALUES(7,3,’19-06-17’,2,4.00);
C. Write SQL statements to list all columns for all tables.
select * from OWNER select * from OWNED_PROPERTY select * from EMPLOYEE
select * from GG_SERVICE
select * from PROPERTY_SERVICE