Database Design with MySQL Workbench: Student and Subject Database

This assignment requires the creation of a database design using MySQL Workbench's EER modeling tool. The student is tasked with developing an Entity-Relationship Diagram (ERD) incorporating appropriate entities, relationships, foreign keys, and primary keys. The assignment emphasizes the selection of suitable data types for attributes derived from a provided list of data items related to students, subjects, and schools. The student must answer questions to demonstrate their understanding of the design, including explaining the rationale behind data item setups, identifying primary keys, and understanding the implications of different relationship types. Furthermore, the assignment includes business rule analysis, requiring the student to address scenarios such as resitting subjects and to formulate SQL queries to identify the highest grade for a student in a subject and calculate a student's age. The final deliverable is a .mwb file containing the database model and the answers to the questions, demonstrating proficiency in database design principles.

Instructions:
 You need to create a database design using MySQL Workbench EER Modelling tool. ERD tutorial
instructions are provided in the course information
 The ERD should have appropriate Entities, Relationships, Foreign Keys and Primary Keys
 The Attributes should have appropriate DataTypes
 Save the model as a .mwb file with your name in the filename.
 Use the data items below in your model.
Data Items (Attributes)
The data items below are (somewhat) self-describing. However, one or two are not as straight forward as
they might seem. It is best if you develop a data name which is descriptive, but not to long. The list
below excludes Primary Keys because it would make the Entities too easy to define. Create appropriate
Primary keys where appropriate (ie Student ID) However, Result ID is not an acceptable PK.
(Excluding the Primary Keys for your entities)
Student first name
Student last name
Contact phone number for student
Student email
Student nationality
Student birth date
Student gender (M/F)
Student date of enrolment ( commencement date at the school)
Student date of enrolment (in a specific subject)
Student grade (Pass Fail etc. )
Student result (0-100)
Date resulted
School name
School address
School phone
School email
Teacher name
Teacher phone
Teacher email
Subject name
Subject description
Subject coordinator
Cost of the subject
Duration of the subject (in hours)
Quantity of assessment items for the subject
Create appropriate primary keys & foreign keys where necessary
Ie Student ID, School ID are OK, however Result ID is not acceptable

Business rules:
 A student has many subjects
 A subject has many students
 A result is recorded for a specific student in a specific subject
 Results are kept as a value between 0 and 100
 Grade is kept as one or two characters (NS,W, F,P,C,D,HD,)
 A student’s enrolment date for a school is a different date from their enrolment in a subject
 A subject has one coordinator (The coordinator would be a teacher)
Out of scope:
 Identifying classrooms and schedules
 Identifying different campuses
 Identifying specific teachers for specific classes or subjects ( a subject coordinator will suffice)
You are required to answer the following questions to demonstrate your understanding of your design.
Understanding the Data Items:
For each item on the list below, complete a table below with an answer to the question and a reason for
setting up the data item in with the chosen structure.
 Student first name
 Contact phone number for student
 Student grade (Pass Fail etc. )
 Date resulted
 Subject coordinator
 Cost of the subject
 Duration of the subject (in hours)
 Quantity of assessment items for the subject
 The primary key for the subject entity
Question Answer Provide the reason why you set the data item up this
way – why not another way / another set up?
What is the Column
Name in the entity?
-- Reason not required --
Which Entity does the
Data item belong to?
-- Reason not required --
Is it a PK? (or part of a
PK?)
Is it a FK ?(from which
table?)
What Data Type has
been chosen?
What Data length has

been chosen?
Is it Null or Not Null
Anything else special
about this data Item?
Understanding the Relationships
Provide a screenshot of the ERD as it is at the current time
 Which of your relationships are “Identifying Relationships” (using Workbench’s notation)?
 What is the implication of having these relationships as Identifying Relationships?
 Which of your Relationships are “non-Identifying Relationships” (using Workbench’s notation)?
 What is the implication of having these relationships as non-Identifying Relationships?
Understanding upgrades to the database
Answers to the following Business rule questions could be written in a paragraph of descriptive text or you
could provide an SQL script with comments to answer the question (or both). Answers to query questions
must be written as a script.
Resitting subjects a second time
A new business rule is introduced: Students should be able to resist a subject in another semester
and the database will retain the original grade and the new grade. What changes (if any) would
you make to the database to satisfy the introduction of this new business rule? Explain why you
would or wouldn’t make changes. Outline any changes that you would make to PKs
Provide a query to identify the highest grade for a student in a subject. The student ID is:
000123456, the subject ID is 4DBB (or any other IDs that work in your database)
Age of a student
Provide a query to identify the age of the student in whole years at the time the query is run. The
student ID is: 000123456,