In this assignment you will design a database system to keep track of information about a college. For this problem you will create 7 main tables / entities (not counting any junction tables). In the description that follows I have bolded 7 of the terms for which you will create tables.

Coastal College is organized by department (Mathematics, Physics, English, and so on). Most departments offer more than one major; for example, the Mathematics department might offer majors in calculus, applied mathematics, and statistics. Each major, however, is offered by only one department. Each faculty member is assigned to a single department. Students can have more than one major, but most students have only one. Each student is assigned a faculty member as an advisor for his or her major; students who have more than one major are assigned a faculty advisor for each major. The faculty member may or may not be assigned to the department offering the major.

A code that has up to three characters (CS for Computer Science, MTH for Mathematics, PHY for Physics, ENG for English, and so on) identifies each department. The combination of the department code and a three-digit number (CS 162 for Programming I, MTH 201 for Calculus I, ENG 102 for Creative Writing, and so on) identifies each course. The number of credits offered by a particular course does not vary; that is, all students who pass the same course receive the same amount of credit.

A two-character code identifies the quarter in which a course is taught (FA for fall, WI, for winter, SP for spring, and SU for summer). The code is combined with two digits that designate the year (for example, FA15 represents the fall semester of 2015).

For a given quarter, there can be multiple sections of each course. For example, 5 different sections of Math 99 might be offered in the Spring quarter. Each section gets a unique four-digit section code. Each section also starts at a different time. The section codes are listed in the schools quarterly schedule, and students use them to register for the correct section.

A section of a course can have many students enrolled in it. The table for keeping track of the students enrolled in a section is where the grades should be stored.

Design:

• • Create the ER Diagram for your design. (Sketch on Paper or use a app to create a diagram)
  • Create tables / entities:
    • Need 7 tables for this assignment.
  • Create at least 5 attributes / fields for each entity (Except for any Junction tables that you will need).
    • The problem description above lists some fields you will need, but can you think of others?
    • You will need to move some attributes around!
    • What data types will you use for the attributes?
  • What type of relationship exists between the tables?
    • Make sure to draw the relationship on your diagram.
    • Note: Foreign Keys should not use the auto-numbered data type!!
  • What are the keys for the tables?
    • What are the common columns?
    • What are the primary keys?
    • What are the foreign keys?

  Implementation:

  • Create the tables in Access.
  • Add the attributes that you decided on.
  • Choose appropriate data types for the attributes.
  • Add Indexes for the most important search columns in each table.
  • Create the relationships that connect the tables.
  • Create forms for each table for entering data.
    • NOTE: You do NOT need to enter data for the tables in this assignment.
  • Create (& Save) at least 4 simple SQL queries that ask something interesting about the data.

Submission:

• A document that includes answers to the questions above, and a photo of your design.
• Your Access database file (.accdb)