case study, you and your team members are asked to design and implement the database component...

             

Transcribed Image Text:

case study, you and your team members are asked to design and implement the database component of a software system that could be used by a Registrar's Office of a College. The various screens of the systems are shown in the screen mockups in the Appendix below. More specifically, your tasks are: Pay attention to the following requirements: A course might be offered in multiple sections (Note that on the mockups, a Section is called a "Course Offering" - They are just synonyms). . A course (eg DBAS27198) can be offerred in multiple sections (eg, 1145_24828, 1145_24123, etc.). Each section is associated with a specific semester (e.g. Fall 2013, Winter 2014), and an instructor. Students are registered with a Section, never with a course. You must write the SQL code to create courses, sections, and add students to course. You must also write the SQL queries to pull the data from the various tables into the grid formats shown on the mockup pages. (Please note that you might have to join or sub-query your tables together to produce the grids exactly as shown.) Please feel free to approach the instructor and clarify anything that is unclear about this case study. 1. Design all the necessary tables where all the relevant data can be efficiently stored 2. Write all the SQL code to support the application . . Remember to treat this case study as a learning challenge and not just a "burden" that you need to get over with. You will learn a lot of useful skills from this project if you treat it as a learning opportunity. Appendix: Screen Mockups Student Dashboard Timothy Jones (991234567) tim.jones@sheridancollege.ca 905 555 1456 (Home) 905 555 1457 (Cell) Edit Profile Current Courses Course Code Course Name Database Design and Implementation Enterprise Application Development DBAS27198 PROG50000 Add Course Academic History Course Code Course Name PROG10008 PROG10009 Java Programming 1 Java Programming 2 Term W 2012 S 2012 Drop Drop Drop Grade A+ A+ Add Course Course Code PROG80000 Course Code Course Name PRO680000 Advanced Enterprise Apps Cancel Search Prerequisite(s) PRO650000 Add Add Registrar Menu Programs Students Students Student# Name 991234567 Timothy Jones 991345432 Mary Green Add Student Courses Instructors Course Offerings Phone Number 905 459 7533 905 459 7533 Email t.j@sheridancollege.ca m.g@sheridancollege.ca Program System Analyst Computer Programmer Edit Edit Edit Registrar Menu Programs Students Programs Program Code Program SA CP EDM Courses System Analyst Computer Programmer Enterprise Database Management Create New Program Instructors Course Offerings Enrolment 535 316 350 Registrar Menu Programs Students Courses Course Code PROG10000 PROG20000 PROG30000 Courses Course Name Introduction to Programming Web Programming Mobile Programming Create New Course Instructors Course Offerings Action Edit Edit Edit Registrar Menu Programs Students Instructors Name Brian Pham John Smith Add Instructor Courses Instructors Course Offerings Phone Number Email 905 459 7533 brian.pham@sheridancollege.ca 123 Oceanview Blvd 905 459 7533 john.smith@sheridancollege.ca |124 Oceanview Blvd Address Edit Edit Edit Registrar Menu Programs Students Course Offerings Offering Code Course Code 10001 PROG10000 10002 PROG10000 10003 PRO620000 10004 PROG30000 New Offering Courses Instructors Course Name Introduction to Programming Introduction to Programming Web Programming Mobile Programming Course Offerings Semester F 2013 W 2014 F 2013 F 2013 Instructor Enrolment Brian Pham Brian Pham John Smith 25 2 15 35 Action Cancel Cancel Cancel Cancel Instructor Dashboard Brian Pham first.last@sheridancollege.co 905 555 1456 Edit Profile My Courses Offering Code Course Code 10001 10003 Course Name DBAS27198 Database Design and Implementation PROG50000 Enterprise Application Development Mark Submit Grade Submit Grade Submit Grade DBAS27198 - Database Design and Implementation Student Name Timothy Jones Mary Green Submit Grade B- A+ Cancel 18 Student PK Student # Home Phone Number Cell Phone Number First Name Last Name Email FK Offering Code FK Program Code DO - +H B B Course Offering PK Offering Code Grade Semester Enrolment FK Course Id FK Instructor Id FK Student # Program PK Program Code Program Name Enrollment FK Student # Et 90 B H PK Course ID Prerequisites FK Offering Code Instructor HPK Instructor Id Course Name InstructorName Course Phone Number Address Email FK Offering Code 1 1 T 1 T A 1 Student Student Number Home Phone Number Cell Phone Number Course Offering Offering Code Course Course Id Program Program Code Instructor Instructor_ld Grade Course_Name Program_Name Name Semester Pre_Requisites Enrollment Phone Number First Name Enrollment Offering Code FK Student Number FK Address Last Name Course_ld FK Email 1 Email Instructor_id FK Offering Code FK Offering Code FK Program Code FK Student Number FK case study, you and your team members are asked to design and implement the database component of a software system that could be used by a Registrar's Office of a College. The various screens of the systems are shown in the screen mockups in the Appendix below. More specifically, your tasks are: Pay attention to the following requirements: A course might be offered in multiple sections (Note that on the mockups, a Section is called a "Course Offering" - They are just synonyms). . A course (eg DBAS27198) can be offerred in multiple sections (eg, 1145_24828, 1145_24123, etc.). Each section is associated with a specific semester (e.g. Fall 2013, Winter 2014), and an instructor. Students are registered with a Section, never with a course. You must write the SQL code to create courses, sections, and add students to course. You must also write the SQL queries to pull the data from the various tables into the grid formats shown on the mockup pages. (Please note that you might have to join or sub-query your tables together to produce the grids exactly as shown.) Please feel free to approach the instructor and clarify anything that is unclear about this case study. 1. Design all the necessary tables where all the relevant data can be efficiently stored 2. Write all the SQL code to support the application . . Remember to treat this case study as a learning challenge and not just a "burden" that you need to get over with. You will learn a lot of useful skills from this project if you treat it as a learning opportunity. Appendix: Screen Mockups Student Dashboard Timothy Jones (991234567) tim.jones@sheridancollege.ca 905 555 1456 (Home) 905 555 1457 (Cell) Edit Profile Current Courses Course Code Course Name Database Design and Implementation Enterprise Application Development DBAS27198 PROG50000 Add Course Academic History Course Code Course Name PROG10008 PROG10009 Java Programming 1 Java Programming 2 Term W 2012 S 2012 Drop Drop Drop Grade A+ A+ Add Course Course Code PROG80000 Course Code Course Name PRO680000 Advanced Enterprise Apps Cancel Search Prerequisite(s) PRO650000 Add Add Registrar Menu Programs Students Students Student# Name 991234567 Timothy Jones 991345432 Mary Green Add Student Courses Instructors Course Offerings Phone Number 905 459 7533 905 459 7533 Email t.j@sheridancollege.ca m.g@sheridancollege.ca Program System Analyst Computer Programmer Edit Edit Edit Registrar Menu Programs Students Programs Program Code Program SA CP EDM Courses System Analyst Computer Programmer Enterprise Database Management Create New Program Instructors Course Offerings Enrolment 535 316 350 Registrar Menu Programs Students Courses Course Code PROG10000 PROG20000 PROG30000 Courses Course Name Introduction to Programming Web Programming Mobile Programming Create New Course Instructors Course Offerings Action Edit Edit Edit Registrar Menu Programs Students Instructors Name Brian Pham John Smith Add Instructor Courses Instructors Course Offerings Phone Number Email 905 459 7533 brian.pham@sheridancollege.ca 123 Oceanview Blvd 905 459 7533 john.smith@sheridancollege.ca |124 Oceanview Blvd Address Edit Edit Edit Registrar Menu Programs Students Course Offerings Offering Code Course Code 10001 PROG10000 10002 PROG10000 10003 PRO620000 10004 PROG30000 New Offering Courses Instructors Course Name Introduction to Programming Introduction to Programming Web Programming Mobile Programming Course Offerings Semester F 2013 W 2014 F 2013 F 2013 Instructor Enrolment Brian Pham Brian Pham John Smith 25 2 15 35 Action Cancel Cancel Cancel Cancel Instructor Dashboard Brian Pham first.last@sheridancollege.co 905 555 1456 Edit Profile My Courses Offering Code Course Code 10001 10003 Course Name DBAS27198 Database Design and Implementation PROG50000 Enterprise Application Development Mark Submit Grade Submit Grade Submit Grade DBAS27198 - Database Design and Implementation Student Name Timothy Jones Mary Green Submit Grade B- A+ Cancel 18 Student PK Student # Home Phone Number Cell Phone Number First Name Last Name Email FK Offering Code FK Program Code DO - +H B B Course Offering PK Offering Code Grade Semester Enrolment FK Course Id FK Instructor Id FK Student # Program PK Program Code Program Name Enrollment FK Student # Et 90 B H PK Course ID Prerequisites FK Offering Code Instructor HPK Instructor Id Course Name InstructorName Course Phone Number Address Email FK Offering Code 1 1 T 1 T A 1 Student Student Number Home Phone Number Cell Phone Number Course Offering Offering Code Course Course Id Program Program Code Instructor Instructor_ld Grade Course_Name Program_Name Name Semester Pre_Requisites Enrollment Phone Number First Name Enrollment Offering Code FK Student Number FK Address Last Name Course_ld FK Email 1 Email Instructor_id FK Offering Code FK Offering Code FK Program Code FK Student Number FK

Answer rating: 100% (QA)

DDL statements Program CREATE TABLE asmntProgram Id INT NOT NULL PorgramCode VARCHAR10 NOT NULL Program VARCHAR30 NOT NULL Enrollemnt INT10 NOT NULLPR... View the full answer