MIT114 Database Design & Development Assignment
| Duedate: | 20nd May 2023, 5:00PM | |
| Weighting: | 40% | |
| Length and Format: | A word document and a SQL file | |
| Assessment Details: | This assignment puts the students in a hypothetical, but realistic, scenario where they design a database intended to support a business application. The assignment is divided into two parts which are intended to reinforce the iterative methodology used in a typical project. Part A requires each student to use business rules to develop a relational database model. Part B, builds on part A, takes a relational database model, and deliver a MySQL implementation of the database. | |
| Assessment addresses SLOs | b: Generate well structured and normalised relational data schemas. C: Create and manage relational databases using Structured Query Language (SQL) employing advanced features. d: Integrate transaction and concurrency control functionality within Relational Database Management Systems (RDBMS). | |
| Marking Criteria | ||
Case Study
Once upon a time, there was a university that offered a wide range of courses for its students. The enrollment process was always exciting for the students as they could choose from a plethora of courses based on their interests and academic goals.
One semester, a group of students enrolled in a course on robotics. The instructor, Dr. Smith, was a renowned expert in the field and had worked on many ground-breaking projects. The students were thrilled to have such a renowned instructor and were eager to learn from him.
The course started with an introduction to the basics of robotics, followed by a series of hands-on projects that involved building robots from scratch. The students worked tirelessly on their projects, putting in long hours in the lab and collaborating with each other to troubleshoot any issues they encountered.
Dr. Smith was impressed with their dedication and hard work, and he went out of his way to provide additional resources and support to help them succeed. He even invited a guest speaker who was a successful entrepreneur in the robotics industry to share his experiences and offer advice to the students.
As the semester progressed, the students became more and more passionate about robotics. They worked on complex projects that challenged them and pushed them to think outside the box. At the end of the semester, they presented their final projects to Dr. Smith and the guest speaker, who were both impressed by their creativity and innovation.
The students walked away from the course with not only a solid understanding of robotics but also a newfound appreciation for the power of collaboration and hard work. They knew that they had learned from one of the best instructors in the field, and they were grateful for the opportunity to enroll in such a transformative course.
This story highlights the importance of having passionate and knowledgeable instructors who can inspire and guide students towards achieving their academic goals. It also emphasizes the value of collaboration and hard work, which are essential for success in any field.
Your task is to create a database that stores employee information, customer information, menu item information and the order details.
To create this database,you must
Assignment Part A
- Identify the entities and their attributes.
- Prepare an ERmodel.
- Normalise the data model to its third normal form.
- Prepare a Relational Database Model.
- Prepare a data dictionary.
Assignment Part-B
- Create a MySQL file that represents your data dictionary in step 5 (Assignment part A). Name it <student.ID> definition.SQL
Note: if your id is 9812345 then you must name your file 9812345-Definition.sql
- Prepare another file <student.ID>Tasks.SQLthat performs the following tasks.
- Each table should have the necessary columns to store relevant information, and should include primary keys and foreign keys where appropriate.
- Create related tables to store information.
- Each table should have the necessary columns to store relevant information, and should include primary keys and foreign keys where appropriate.
- Ensure that each column has an appropriate data type and any necessary constraints (e.g. NOT NULL, UNIQUE, etc.).
- Insert at least 10 students records for each table.
- Write SQL queries and Stored Procedure to retrieve the following information:
- All students who are enrolled in a specific course.
- All courses taught by a specific instructor.
- The number of students enrolled in each course.
- The average grade for each course.
- The top 10 students based on their GPA.
Submission Requirements
Students must submit their Assignments through CANVAS shell. Email submission will not be accepted.
Deliverables:
Assignment Part -A must be completed and documented using MS Word. All drawing must be drawn using draw.io software.
Assignment Part -B requires you to createtwo SQL files with extensive comments in them for the marker to read and understand the code. These SQL files must run without any external inputs or Modifications.
Put those 3 files: Word document and two SQL files in a Folder. Compress it (ZIP it) and submit the Zipped file through CANVAS Shell before the Deadline.