Task 1: Creating the database [42 marks]

1.  Use MySQL Workbench to create an ERD to present a relational database model            supplied as below. This is a sample ERD for a simplified University Enrolment Database.

• A faculty means an academic member who usually runs (teaches) courses (subjects).

• A course means a subject run by University

• A course is offered by the schedule set by University

• A student make a registration for each term by enrolling to a number of courses offered.

• An attribute in bold type means it is set as ‘Not Null’.

PKs should be correctly specified. All necessary attributes should be specified by setting appropriate data types and appropriate field lengths. [Save the completed model as a uniEnrolDB.mwb file]

The final ERD you create should correctly correspond with the ERD provided above in terms of structure, though the way to present components (PK or FK) in the ERD created using MySQL Workbench is not exactly same as what is presented in the ERD provided above

2.   Create a physical database model using the forward engineering process on MySQL Workbench.

3.  Insert the data (provided for this assignment) into the database. You may choose to use either the SQL INSERT syntax or the import facility provided by MySQL Workbench. (Caution: The  name or order of attributes  in the  raw data files  provided  may  not exactly match with those shown in the ERD. You will have to be careful to check it when you import data from Excel files to each table of your database)

4.  Finally, dump the database into one integrated file on MySQL Workbench. [Save the file as uniEnrolDB.sql file]

Task 2: Creating queries [40 marks]

Using the database you constructed in Task 1, create the following SQL queries in MySQL Workbench.

For each question, the correct result expected is provided to help you get an idea of what the table head and data format of your query result should look like.

Note:

Each question shows the correct result expected by the correct query. Please note that this

result will be generated as it is, only if your database is developed correctly as specified in

this assignment. If your database is not built up fully or incorrectly, the result may be

different even if your SQL code is correct and ideal.

When your marker does marking on your SQL submission, the marker will test your code

in their own correct database and also will assess your SQL codes by checking if the code

has correct logic and syntax.

[Paste your SQL query into a Word document to submit. The final Word file should contain all SQL queries you made for this task]

Notes:

Retrieve the name, city, and grade point average (GPA) of students with a high GPA (greater than or equal to 3.2). Save this query as Q-1.

2.  List the name, city, and increased salary (increase the salary by 20 percent) of faculty hired after 1996. Save this query as Q-2.

3.  List the offering  number and course  number  of Year 2006 offerings which  had  no instructor (faculty) assigned. Save this query as Q-3.

4.  List the offer number, course number, offer term, offer year and faculty Social Security number (SSN) for offerings scheduled in fall 2005 or spring 2006. Save this query as Q-4.

5.  List the offering number, course number, and days of offerings containing the words “finance” or “database” in the course description (regardless of uppercase or lowercase  letters) and taught in winter 2006. Save this query as Q-5.

6.  List the offer number, course number, and full name of the instructor (faculty) of all FINANCE courses (the course number’s prefix is ‘FIN’) offered in winter 2006 taught by professor. Note: professor’s rank is “PROF” in the database. Save this query as Q-6.