You are asked to design and implement a Database for a new University called MTUC as
MTUC has Academic staff, general staff and students who are working or attending this new
University. Each person is either academic staff, or a general staff, or a student. A person is
uniquely identified by a PerId (person's ID), and has a Name, and an Address. An Address is
composed of HouseNo, Street, and City. A characteristic property of a student is that
she/he has 1 course and 12 modules for this course in one year. An academic staff has a
Position (Head of Department, Lecturer, Assistant Lecturer) and an academic qualification.
A general staff has a job title. An academic staff teaches at most one course, whereas a
student takes at one course with many (12) modules. A course is uniquely identified by a
CourId (course ID), and has a CourName (course name). Each course is taught by at least
one academic staff, and can be taken by many students, but there may be courses that are
not taken by any students. Each course can use more than one textbook, but there may be
courses with no textbook.
Your design must enforce:
Uniqueness on important data items e.g. each Student, Staff, Course, etc
You can make any assumptions about the data that you feel are relevant.
Project Tasks and Submissions:
1. List the Entities, Attributes and Relationships and Multiplicities. (30 Marks)
2. Draw an ER Diagram using the information from step 1. This can be done using the
drawing tools in MS Word, or by drawing it by hand and taking a picture to include
within your submission. (20 Marks)
3. Implement the design from steps 1 &2 on your MySQL server. All CREATE TABLE,
DROP TABLE statements must be provided in script form (.sql file) and should be
included in your submission. You must indicate all Primary, foreign and candidate
keys. (20 Marks)
4. Devise sample data i.e. make up at least 4 rows of data for each table. Provide the
Insert statements in script form (.sql file) and should be included in your submission.
5. Write FIVE SQL multi-table JOIN queries that demonstrate the functioning of your
database design. All tables should be used in those queries - essentially you will be
expected to formulate requests that require you to write 3/4/5 table joins to
complete this part successfully. Include the queries in your MS Word/PDF Document
65 freelanceria on tarjonnut keskimäärin €159 tähän työhön
I have more than 13 years of experience in sql development in different database like oracle/db2/mysql and worked in more than 300 projects. Lets discuss your requirement.