Database Design Final Project Students Get Together SQL Server Database
This exam stresses the concepts of building the database correctly and being able to extract accurate information from It. While the problem is a simple one, logical thought is required to reach the end result.
Well, you have all struggled and made your best efforts. I know that much time and effort went into completing all the work for this course. And now you all need to blow off some steam. So, you all decide to get together to celebrate the conclusion of the course. Everyone wants to celebrate and someone suggests that you get together to go out for a dessert (or two). Now we know everyone has his or her own tastes, and we wouldn’t want to go somewhere that doesn’t have a dessert we like. All the local eateries have certain kinds of dessert they serve. They surely can’t serve every kind of dessert.
You will need to design a database to keep track of all the students in the class. You will also track what kinds of dessert each student likes. A student may like many different desserts, and many students may like the same dessert. Also, we need to keep track of all the restaurants In the area, and all the different desserts they serve. A restaurant may serve many desserts, and many restaurants may serve the same dessert.
Use DBDL to design the tables (fields, keys, relationships, etc.) necessary to track the information as described above.
Explain any assumptions. Make sure all table are in 3NF and the correct relationships exist between the tables.
Use the SQL Server Management Studio to create the database, tables, relationships, and diagram based on the DBDL.
After the Database is created, you will create the six queries listed below Save the queries as Ql. Q2, Q3, Q4, 05 and Q6.
1. Create the SQL to show what desserts a restaurant serves. (Show all restaurants and each dessert that they serve.)
2. Create the SQL to show what desserts a student likes. (Show all students and each dessert that they like.)
3. Create the SQL to show students who like Apple Pie.
4. Create the SQL to show the NUMBER of restaurants that serve Apple Pie.
5. Create the SQL to show the most popular dessert. (Based on student likes, not restaurants where it is served.)
6. Create the SQL to show all the restaurants that each student can go to and have at least one dessert that they like. I.e. – If a restaurant has a dessert that the person likes, then they can go to that restaurant. (Show all students and restaurants that each can go to.)
Create the database diagram of your tables.
Submitting Your Final Project:
Create a Folder Called FinalProjet_YourLastNarne. Put the DBDL (create in Word and Save as a .PDF file), Queries and the .mdf and .ldf Database files in the
FinalProjeel Folder, zip the folder and submit it to the assignment.