Question: Using SQL II . Sunny Skies Landscaping LLC , Functional Dependencies, Normalization Sunny Skies Landscaping LLC is a fictional database scenario that will be implemented
Using SQL II Sunny Skies Landscaping LLC Functional Dependencies, Normalization Sunny Skies Landscaping LLC is a fictional database scenario that will be implemented and resourced throughout the remainder of the class as a class project. Initially this week we will study the design of the database in rd normal form, looking at functional dependencies and tables in DBDL format. In future weeks to come, the class will write Data Definition Language DDL scripts to load the tables and data, evaluate and script the database relationships, develop functions and stored procedures to manipulate the data using Data Manipulation Language DML and study and implement indexes for query performance. This is much of what is entailed in a realworld database project. Note to Students: This is an excellent project that can be expanded upon if you want to further demonstrate your abilities to prospective employers. This is entirely up to you, and is not a part of this course. Thoughts on what could be added to this project are: equipment and maintenance, parts and inventory, sales, billing, among others. On with the project...Sunny Skies Landscaping LLC is a new startup company with a focus on providing lawn care services for modest residential properties in the range of acres. The company has enlisted you, the technical database expert, to help them design a database system to encompass their customers, and jobs. The following are assumptions based on the planned business strategy: a Sunny Skies Landscaping LLC will want to store contact information name address, phone, etc. about each customer. b Sunny Skies Landscaping LLC will also want to keep track of the many landscaping jobs that might be performed for customers. In this regard, a customer can be scheduled for many jobs and a given job can be applicable to many customers. Of important note is that a job will have both a standard price for each job type and a quoted price for each job and customer. The standard price is used to provide a rough basis for costing of all jobs of a particular type based upon predetermined factors. The quoted price is the agreed upon price with the customer to perform a particular job. Hints: When completing the functional dependencies, watch for fields that are dependent on more than one key. Refer to the notes above concerning relationships. From the information provided above, list the functional dependencies derived from the following unnormalized tables the inner parenthesis represents a repeating group: a Customers CustomerID FirstName, LastName, Address, City, State, ZipCode, PhoneNumber, EmailAddress JobID DateScheduled, DateCompleted, QuotedPrice JobName StandardPrice With the results from question above, convert ALL tables to rd normal form. List all of the tables in DBDL format, indicating all keys.
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
