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 3rd 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 real-world 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 .25-.4 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 field(s) that are dependent on more than one key. Refer to the notes above concerning relationships. 1. From the information provided above, list the functional dependencies derived from the following un-normalized 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))2. With the results from question 1 above, convert ALL tables to 3rd 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 blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!