Business Rules: Your database needs to store the following information (at a minimum). Note that these are NOT the tables that you will be creating. From this information, and after going through Parts 1-3 of this project, you will be able to figure out the tables to create in Part 4. • For each Product: o Product name o Department o Price • For each Customer: o First and last name o SSN o List of products bought o List of credit cards they have saved in the system. Each credit card should have: ? Name on Card ? Credit Card Number ? Maximum Spending Limit ? Amount Owed ? Expiration Date • For each Employee: o First and last name o SSN o Department o Manager. Each department has 1 or no managers. A manager can also have another job role (as a regular employee) o Salary. Each Department also has a ? Minimum Salary ? Maximum Salary o Bank Account to deposit salary. Each bank account has: ? Owner ? Bank Name ? Routing Number ? Account Number Data: Employee Data Name SSN Employee Salary Manager Department Min Dept. Salary Max Dept. Salary Bank Name Bank Routing Number Bank Account Number Bank Account Holder Bugs Bunny 123- 568- 9898 100,000 Management 80,000 120,000 Manchester Bank 102030 111111 Bugs Bunny Tweety 111- 222- 3333 90,000 Management 80,000 120,000 Manchester Bank 102030 222222 Daffy Duck Daffy Duck 444- 111- 2222 73,000 Tweety Accounting 70,000 95,000 Manchester Bank 102030 222222 Daffy Duck Popeye 192- 981- 9091 82,000 Tweety Accounting 70,000 95,000 Old Farms Bank 997788 111222 Popeye Winnie the Pooh 120- 191- 9811 55,000 Popeye Cashier 50,000 80,000 Old Farms Bank 997788 919191 Winnie the Pooh Mickey Mouse 234- 987- 1918 60,000 Popeye Cashier 50,000 80,000 Green Land Bank 777777 101010 Snoopy Snoopy 651- 625- 2811 70,000 Popeye Cashier 50,000 80,000 Green Land Bank 777777 101010 Snoopy
Product Data Name Department Price Tylenol Pharmacy $6.99 Advil Pharmacy $7.99 Kleenex Pharmacy $1.89 Chocolate Food $1.29 Apples Food $0.99 Pants Clothing $7.99 Gloves Clothing $2.09 Wrench Tools $6.09 Scissors Tools $2.99 Customer Data Information in the Credit Card columns is in the following order: – Name on credit card – Credit card number – Max spending amount – Expiration date – Amount owned Name SSN List of Products Bought Credit Card 1 Credit Card 2 Credit Card 3 Credit Card 4 Charlie Brown 123-222- 9898 Tylenol Chocolate Apples Wrench Charlie Brown 10901 $2,000 1/4/2020 $80 Charlie Brown 22222 $2,500 8/14/2023 $120 Snow White 111-555- 3333 Wrench Scissors Advil Snow White 23232 $3,000 11/6/2022 $92 Goofy 120-666- 9811 Tylenol Pants Apples Wrench Yogi Bear 555 -568 – 9898 Gloves Pants Chocolate Advil Tylenol Yogi Bear 23277 $1,000 1/16/2023 $100 Yogi Bear 22277 $1,500 6/17/2023 $200 Yogi Bear 67677 $2,000 1/22/2020 $300 Yogi Bear 55555 $2,000 4/11/2021 $700 Cinderella 888 -222 – 3333 Gloves Pants Chocolate Advil Tylenol Prince 19991 $2,000 7/4/2021 $85 Cinderella 22888 $2,500 9/14/2025 $124 Woody Woodpecker 999 -111 – 2222 Batman 345 -981 – 9091 Gloves Pants Advil Tylenol Batman 27777 $1,500 1/24/2023 $100 Yogi Bear 22299 $1,500 2/19/2023 $200 Yogi Bear 888888 $2,500 6/20/2020 $80 Yogi Bear 556677 $2,000 5/12/2021 $400 Little Red Riding Hood 222 -987 – 1918 Wrench Scissors Advil Chocolate Part 1: (5 Points) Conceptual Model: Determine the entities involved and the relationships between the various entities. ? Submit as a bulleted list of points. Part 2: (10 Points) Logical Model: Determine the attributes your entities should store and the relationship types between the various entities. You can use SQL Data Modeler or just draw boxes and arrows using another program like Word, PowerPoint, Paint, etc. ? Submit your drawing of the entities and the relationships. Part 3: (30 Points) Normalization: Using the logical model and data provided, run through the 3 levels of normalizations. ? Submit the definition of each normalization step and how the data in the tables looks before and after each normalization step. Do not over split
Part 4: (30 Points) Physical Model: Determine the table names, column names, column data types, primary keys and constraints. • All constraints need to be named • All tables should have primary keys • Salaries should be > 0 • SSN should be in the form of xxx-xxx-xxxx ? Submit your DDL file and a screenshot of running all commands successfully. Part 5: (15 Points) Inserting the Data: Insert the data provided into the created tables. ? Submit your DML file containing all the insert commands and a screenshot of successfully running all commands. Part 6: (30 Points) Gathering Statistics: Write SQL statements to do the following. ? Submit your SQL commands and screenshots of running these commands. No. Task What you’re trying to accomplish SQL Command(s) Screenshot(s) 1 View the data in all the tables. 2 Display the name of the product bought the least number of times. 3 Display the name of the customer that has the maximum number of credit cards. 4 For each customer, display the total owed. 5 An SQL command using avg 6 An SQL command using sum, group by on two columns and having 7 An SQL command using a right join 8 An SQL command using an inner join 9 An SQL command that uses the update keyword. 10 An SQL statement that uses like and order by
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more