CP2404 Database Modelling - Assignment #2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
CP2404 Database Modelling - Assignment #2
Assignment 2 – Database Implementation and Query Formulation
Rationale
This assignment has been designed to give students experience using Structured Query Language (SQL) and other database management systems (DBMS) facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following learning objectives for this subject:
• Develop and implement a database model using the E-R model and facilities provided by a DBMS
• Formulate queries using a database query language
This assignment consists of two main tasks:
1. Creating the database (by following three subtasks):
o Create a relational database for a given conceptual model (ERD) using MySQL Workbench
o Create a physical database model on MySQL Workbench by applying the forward engineer process
o Import raw data from the external file to a table using the MySQL Workbench facility or using SQL queries
2. Writing SQL queries for given problems.
Further details about each task are presented in the following pages.
Submission
• A MySQL Workbench file containing the ERD you created (.mwb)
• A database dump file built and exported on MySQL Workbench (.sql)
• A WORD or text file containing all SQL query codes and result tables (.doc, .docx, or .txt)
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:
- |
Queries should be written so that they would work with all reasonable sets of test data, not just that which has been supplied as a sample data. |
- |
Marks may be deducted if your SQL is excessively (or unnecessarily) complicated. |
- |
Full marks will be awarded where the solution provided is correct in all respects. |
- |
Partial marks may be allocated where students are deemed to have provided a significant effort toward a correct result, but the solution contains some error. |
- |
No marks are awarded where either no solution is provided, or the solution provided is deemed to be mostly incorrect. |
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.
StdFirstName |
StdLastName |
StdCity |
StdGPA |
CANDY |
KENDALL |
TACOMA |
3.50 |
JOE |
ESTRADA |
SEATTLE |
3.20 |
MARIAH |
DODGE |
SEATTLE |
3.60 |
TESS |
DODGE |
REDMOND |
3.30 |
CRISTOPHER |
COLAN |
SEATTLE |
4.00 |
WILLIAM |
PILGRIM |
BOTHELL |
3.80 |
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.
FirstName |
LastName |
City |
InflatedSalary |
HireDate |
NICKI |
MACON |
BELLEVUE |
78000 |
4/11/1997 |
CRISTOPHER |
COLAN |
SEATTLE |
48000 |
3/1/1999 |
JULIA |
MILLS |
SEATTLE |
90000 |
3/15/2000 |
3. List the offering number and course number of Year 2006 offerings which had no instructor (faculty) assigned. Save this query as Q-3.
OfferNo |
CourseNo |
1111 |
IS320 |
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.
OfferNo |
CourseNo |
OffTerm |
OffYear |
FacSSN |
1234 |
IS320 |
FALL |
2005 |
098765432 |
3333 |
IS320 |
SPRING |
2006 |
098765432 |
4321 |
IS320 |
FALL |
2005 |
098765432 |
5679 |
IS480 |
SPRING |
2006 |
876543210 |
7777 |
FIN480 |
SPRING |
2006 |
765432109 |
9876 |
IS460 |
SPRING |
2006 |
654321098 |
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.
OfferNo |
CourseNo |
OffDays |
5555 |
FIN300 |
MW |
5678 |
IS480 |
MW |
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.
OfferNo |
CourseNo |
Instructor Name |
5555 |
FIN300 |
NICKI MACON |
7. Summarize the number of offerings by course. Save this query as Q-7.
CourseNo |
NumOfferings |
FIN300 |
1 |
FIN450 |
1 |
FIN480 |
1 |
IS320 |
6 |
IS460 |
2 |
IS480 |
2 |
2021-12-26