1. How could a well-managed database help the company better achieve its goals?
ID: 3820924 • Letter: 1
Question
1. How could a well-managed database help the company better achieve its goals?
2. What are the modification problems that are likely to occur if Garden Glory attempts to maintain the list below in a spreadsheet?
PropertyName
Type
Street
City
Zip
ServiceDate
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/5/2008
Elm St Apts
Apartment
4 East Elm
Lynwood
98223
5/8/2008
Jefferson Hill
Office
42 West 7th St
Bellevue
98040
5/8/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/10/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/12/2008
Elm St Apts
Apartment
4 East Elm
Lynwood
98223
5/15/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/19/2008
3. Using these data presented in the list, 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. Indicate what assumptions need to be checked with the users (GG) and what suggestions you may have about the determinants/keys (e.g., do you need a surrogate key?)
4. Based on the above analysis and assumptions, split the sample list into tables such that each has only one theme. Demonstrate that the modification problems you identified in E have been eliminated.
5.Assume that based on your suggestions and their additional requirements, Garden Glory designs a trail database saved as Garden_Glory.accdb in MS Access 2013. Query the trail database using SQL statements and answer the following questions. For each SQL statement you write, show the results based on your data (you could press the "PrtSc - Print Screen" key to capture the results image) .
5.1) Write SQL statements to list all columns for all tables.
5.2) Write an SQL statement to list the LastName , FirstName and CellPhone for all employees having an experience level of Master .
5.3) Write an SQL statement to list the LastName , FirstName and CellPhone for all employees having an experience level of Master and a FirstName that begins with the letter J .
5.4) Write an SQL statement to list the Name of employees who have worked on a property in New York .
5.5) Write an SQL statement to list the names of employees who have worked on a property owned by a Corporation .
5.6) Write an SQL statement to show the name and sum of HoursWorked for each employee.
5.7) Write an SQL statement to show the sum of HoursWorked for each ExperienceLevel of EMPLOYEE. Sort the results by ExperienceLevel in descending order.
5.8) Write an SQL statement to show the sum of HoursWorked for each Type of OWNER but exclude services of employees who have an ExperienceLevel of Junior and exclude any Type with less than three members.
PropertyName
Type
Street
City
Zip
ServiceDate
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/5/2008
Elm St Apts
Apartment
4 East Elm
Lynwood
98223
5/8/2008
Jefferson Hill
Office
42 West 7th St
Bellevue
98040
5/8/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/10/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/12/2008
Elm St Apts
Apartment
4 East Elm
Lynwood
98223
5/15/2008
Eastlake Building
Office
123 Eastlake
Seattle
98119
5/19/2008
Explanation / Answer
1. A well managed database makes it very simple to perform data manipulation and access operations in a very easy and manageble way.. The cost of maintenance is reduced and It offers better data representation.
2. If the data is maintained in form of spreadsheet like shown in the question, There can be multiple problems, when it comes to manipulation of the data. Suppose we want to change the addresss associated with a particular property name. In this case, we need to change the address at multiple places in the spreadsheet, as the sheet contains repeated data. Also, Due to the repetition of the data, it takes more and more space to keep even a very little information.
3. From the given data, it is visible that there are some properties, which have their own addresses. Also, each property belong to a particular Property Type. The property requires service also on different dates.
4. From the above assumptions, we can break the given data to different entites such as
Property: Contains property name, street, city and zip, contains reference to property type.
PropertyType: Contains rows for different type of property types possible
Service: Contains reference to property and the data on which servce has been done
You may want to break street, city and zip into some address table further.. again.. that depends on the requirement of how extensive your data set is..
I can answer just first four subquestions due to the time constraints.. i request you to please post the remaining questions in a seperate thread.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.