This my second order.Just do Task 2 Question 14 to 20, the database is provided.Thank you.
Notes:
-
|
Learning SQL is an essential part of this subject. Using the Access GUI (QBE facilities
using Query Wizard) to produce your SQL queries (for Task 2) is not acceptable. No
marks will be awarded where it is deemed that the MS Access query builder has been
used to obtain results for Task 2. |
-
|
Your knowledge of SQL will be also tested in the final exam. |
-
|
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 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. |
You have been commissioned to design a database to manage the i CP2404 CP5633 Assignment Part 2 Page 1 Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others. Assignment 2 – Database Implementation and Formulating Queries Due date: 5pm Friday 25 May 2018 Assessment Weight: 15% Rationale This assignment has been designed to give students experience using SQL and other DBMS facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following two subject learning outcomes (as presented in the Subject Guide): 1. Implement a database design as a relational database using Microsoft Access. 2. Formulate queries in SQL. Task 1 Creating the Database [54 marks] 1. Use MS Access to create tables identified in the relational data model supplied below (a sample ERD for a simplified University Database). Primary keys should be correctly specified. All necessary attributes should be specified by setting correct data types and appropriate field lengths. CP2404 CP5633 Assignment Part 2 Page 2 2. Insert the data supplied (as MS Excel files) into the database. You may choose to use the SQL INSERT syntax or alternatively the import utility provided by MS Access. (Note: the name or order of attributes in the raw data files may not be exactly matching with those presented in the ERD) 3. Establish relationships between the tables in MS Access (using Relational Diagram). The final relationship diagrams should correctly correspond with the JM Books ERD supplied. (Note that the way to present components (PK, FK, relationships) of the relational diagram in MS Access may not be exactly same as what was presented in the ERD provided) Task 2 Creating Queries [40 marks] Using the database you constructed in Task 1, create the following SQL queries in MS Access. The result table of each query should look like the one provided for each question. 1. Retrieve the name, city, and grade point average (GPA) of students with a high GPA (greater than or equal to 3.7). Save this query as Q-1. Q-1 StdFirstName StdLastName StdCity StdGPA CRISTOPHER COLAN SEATTLE 4.00 WILLIAM PILGRIM BOTHELL 3.80 2. List the name, city, and increased salary (increase the salary by 10 percent) of faculty hired after 1996. Save this query as Q-2. Q-2 FacFirstName FacLastName FacCity InflatedSalary FacHireDate NICKI MACON BELLEVUE 71500 11-Apr-97 CRISTOPHER COLAN SEATTLE 44000 01-Mar-99 JULIA MILLS SEATTLE 82500 15-Mar-00 CP2404 CP5633 Assignment Part 2 Page 3 3. List the offering number and course number of summer 2006 offerings without an assigned instructor (faculty). Q-3 OfferNo CourseNo 1111 IS320 4. List the offer number, course number, and faculty Social Security number (SSN) for course offerings scheduled in fall 2005 or winter 2006. Q-4 OfferNo CourseNo FacSSN 1234 IS320 098-76-5432 4321 IS320 098-76-5432 4444 IS320 543-21-0987 5555 FIN300 765-43-2109 5678 IS480 987-65-4321 6666 FIN450 987-65-4321 5. List the offering number, course number, days, and time of offerings containing the words “database” or “programming” in the course description and taught in spring 2006. Q-5 OfferNo CourseNo OffDays OffTime 3333 IS320 MW 8:30 AM 5679 IS480 TTH 3:30 PM 6. List the offer number, course number, and name of the instructor of IS course offerings schedules in fall 2005 taught by assistant lecturer. Note: assistant lecturer’s rank is “ASST” in the database. Q-6 OfferNo CourseNo FacFirstName FacLastName 1234 IS320 LEONARD VINCE 4321 IS320 LEONARD VINCE CP2404 CP5633 Assignment Part 2 Page 4 7. Summarize the number of offerings by year. Q-7 OffYear NumOfferings 2005 3 2006 10 8. Summarize the average GPA of upper-division (junior or senior) students by major. Only list the majors with average GPA greater than 3.1. Q-8 StdMajor AvgGPA ACCT 3.5 IS 3.14999997615814 9. Summarize the minimum and maximum GPA of students by major and class. Q-9 StdMajor StdClass MinGPA MaxGPA ACCT JR 3.5 3.5 ACCT SO 3.3 3.3 FIN JR 2.5 2.7 FIN SR 3.2 3.2 IS FR 3 3 IS JR 3.6 3.6 IS SO 3.8 3.8 IS SR 2.2 4 10. Summarize the number of IS course offerings by course description. Q-10 CrsDesc OfferCount FUNDAMENTALS OF BUSINESS PROGRAMMING 6 FUNDAMENTALS OF DATABASE MANAGEMENT 2 SYSTEMS ANALYSIS 2 11. List Leonard Vince’s teaching schedule in fall 2005. For each course, list the offering number, course number, number of units, days, location, and time. CP2404 CP5633 Assignment Part 2 Page 5 Q-11 OfferNo CourseNo CrsUnits OffDays OffLocation OffTime 1234 IS320 4 MW BLM302 10:30 AM 4321 IS320 4 TTH BLM214 3:30 PM 12. List Bob Norbert’s course schedule in spring 2006. For each course, list the offering number, course number, days, location, time, course units, and faculty name. Q-12 OfferNo CourseNo OffDays OffLocation OffTime CrsUnits FacFirstName FacLastName 5679 IS480 TTH BLM412 3:30 PM 4 CRISTOPHER COLAN 13. List faculty members who have a higher salary than their supervisor. List the Social Security number, name, and salary of the faculty and supervisor. Q-13 Subr.FacSSN Subr.FacLastName Subr.FacSalary Supr.FacSSN Supr.FacLastName Supr.FacSalary 987-65-4321 MILLS $75,000.00 765-43-2109 MACON $65,000.00 14. ist the names of faculty members and the course number for which the faculty member teaches the same course number as his or her supervisor in 2006. Q-14 FacFirstName FacLastName CourseNo LEONARD VINCE IS320 LEONARD FIBON IS320 15. List the course number, the offering number, and the average GPA of students enrolled. Only include courses offered in fall 2005 in which the average GPA of enrolled students is greater than 3.0 Q-15 CourseNo OfferNo AvgGPA IS320 1234 3.22499996423721 IS320 4321 3.15999999046326 CP2404 CP5633 Assignment Part 2 Page 6 16. For offerings beginning with IS in the associated course number, retrieve the offer number, the course number, the faculty number, and the faculty name. Include an offering in the result even if the faculty is not yet assigned. Q-16 OfferNo CourseNo offering.FacSSN faculty.FacSSN FacFirstName FacLastName 1111 IS320 2222 IS460 1234 IS320 098-76-5432 098-76-5432 LEONARD VINCE 3333 IS320 098-76-5432 098-76-5432 LEONARD VINCE 4321 IS320 098-76-5432 098-76-5432 LEONARD VINCE 4444 IS320 543-21-0987 543-21-0987 VICTORIA EMMANUEL 8888 IS320 654-32-1098 654-32-1098 LEONARD FIBON 9876 IS460 654-32-1098 654-32-1098 LEONARD FIBON 5679 IS480 876-54-3210 876-54-3210 CRISTOPHER COLAN 5678 IS480 987-65-4321 987-65-4321 JULIA MILLS 17. Retrieve the Social Security Number (SSN), name (first and last), department, and salary of faculty who are not students. Q-17 FacSSN FacFirstName FacLastName FacDept FacSalary 098-76-5432 LEONARD VINCE MS $35,000.00 543-21-0987 VICTORIA EMMANUEL MS $120,000.00 654-32-1098 LEONARD FIBON MS $70,000.00 765-43-2109 NICKI MACON FIN $65,000.00 987-65-4321 JULIA MILLS FIN $75,000.00 18. List the name (first and last) and department of faculty who are only teaching in winter term 2006. Q-18 FacLastName FacFirstName FacDept EMMANUEL VICTORIA MS MILLS JULIA FIN 19. List the course number, the course description, the number of offerings, and the average enrollment across offerings. CP2404 CP5633 Assignment Part 2 Page 7 Q-19 CourseNo CrsDesc NumOfferings AvgEnroll IS320 FUNDAMENTALS OF BUSINESS PROGRAMMING 3 4 IS460 SYSTEMS ANALYSIS 1 6 IS480 FUNDAMENTALS OF DATABASE MANAGEMENT 2 5 20. List the Social Security number (SSN) and the name (first and last) of faculty who teach at least one section of all of the fall 2005 information systems courses. Q-20 FacSSN FacFirstName FacLastName 098-76-5432 LEONARD VINCE Submission • Submit an MS ACCESS database file to LearnJCU. (The final database file should contain all tables, queries and reports constructed for Task 1 and Task 2.) • The timestamp shown on LearnJCU assignment submission will be used to determine if the submission is late or not. Late submissions will subject to penalty. Marking Criteria Criteria Exemplary Good ~ Satisfactory Limited Unacceptable Task 1: Creating Database Attributes (Fields) (For each table) 3 All attributes are included and correctly named. AND Data type is defined correctly on all attributes. 2 All attributes are included and most of them are correctly named but data type is defined incorrectly on one or two attributes. 1 Some attributes are missing, not correctly named or data type is defined incorrectly on many attributes. 0 Most attributes are missing or mostly named incorrectly or data type is defined incorrectly on the most attributes. Primary Keys (For each table) 1 An attribute (field) has been selected as a primary key correctly 0.5 Attempted to set a primary key