Database Design You are going to design a database system fo

Database Design

You are going to design a database system for a small university.   The database will be used for scheduling classes.   The university consists of multiple schools, which each having multiple departments.

For this assignment I want you to use a crows-foot notation.   You will need to:

Define the tables that you need in the database

Define the attributes that need to be included in the tables

More attributes (e.g., unique ID’s) may need to be added to the tables

Some attributes may need to be split apart (e.g., Name – split into -> LNAME, FNAME, MINITIAL)

Define the relationships (e.g., 1-to-1, 1-to-many, many-to-many) between the tables

You will need to identify the primary and foreign keys.

DO NOT worry about differentiating between strong and weak relationships

The following are the business rules for the university:

Each school consists of multiple departments, but a department can only belong to a single school.

Each department can offer many different classes and each class offered may have multiple sections.  

A class is only offered through one department and each section maps to a single class.

A section of a class is assigned to a single classroom at a specific time, but a classroom may have several sections assigned to it.

Departments may employ many faculty members and each faculty member is employed by one department.

A professor (faculty member) can teach many sections, but a section is taught by a single faculty member.

Multiple students can sign up for many sections and sections may have many students.

A student has one faculty advisor, but an advisor can advise many students.

Information maintained about the different entities includes:

School

School name (e.g., Business, Engineering, etc.)

Building name (e.g., Gorman, Agnese-Sosa, etc.)

Office number

Dean name

Phone Number

Mail Stop

Department

Department name (e.g., Management Information Systems, Management, etc.)

Coordinator name

Building name

Office number

Phone number

Mail stop

Class

Course number (e.g., MIS2321)

Course name (e.g., Introduction to MIS)

Section

Unique ID number (e.g., CRN00101)

Section number (e.g., 1,2,3,4)

Room

Building name (e.g., Gorman, ATT, etc.)

Room name/number (e.g., 107, 104, etc.)

Capacity

Faculty

Faculty ID

Faculty name

Building name

Office number

Office phone

Title

Student

Student ID Number

Student Name

Phone Number

Address

Major (e.g., Management Information Systems, Management, etc.)

Major2

Minor

Solution

1) School

Create table school(school_name varchar2(10), building_name varchar2(10),office_number number(10) UNIQUE ,dean_name varchar2(10), phone_no number(10) UNIQUE,mail varchar2(25) UNIQUE,depart_name varchar2(10) PRIMARY KEY(school_name,depart_name));

2) Department

Create table department(depart_name varchar2(20), class number(5) ,coordinator varchar2(10),office_number number(10),faculty varchar2(10), FOREIGN KEY(depart_name) REFERENCES school(depart_name));

3) Class

Create table class(class number(5), students_count number(5), sections number(5), course_number number(5),course_name varchar2(10));

4) Section

create table section(section_id number(5) UNIQUE,section_number number(2),strength number(3),faculty varchar2(10) UNIQUE);

5) Faculty

create table faculty(fid number(5), fname varchar2(10),departname varchar2(10) UNIQUE,section number(3),advises_about varchar2(10));

6)Student

create table student(snumber number(5) UNIQUE, sname varchar2(10), address varchar2(20),phonenumber number(10),major varchar2(10),minor varchar2(10),advisor number(5), FOREIGN KEY(advisor) REFERENCES faculty(fid));