IT 330 Database Management Systems (Final Project)

IT 330 Database Management Systems

Final Project

Deadline: Sunday, July 28, 6pm. No late work accepted.

1.      Create a database for a school registration system.  The registration system should be able to hold information for the following entities:

a.      Students

b.      Courses

c.       Teachers

d.      Grades

At the minimum, the following queries should be answered:
- how many students are enrolled in a class?

-          How many courses are available on a Wednesday (or any given day)?

-          Who are the teachers teaching a particular course?

-          What is the average GPA for a course this semester?

2.      Create an ERD (entity-relationship diagram) that shows the relationships of all your tables.

3.      Normalize your tables.

4.      Create a one-page summary/documentation on how you were able to construct your database.  What are the (possible) pain points that still exist and how do you plan to address them in the future? 

5.      Submit your queries, database definitions, and diagram in either a Word file or (for the queries and results) a screenshot of your SQL developer commands and results.  Zip them all up into one zip file and upload to Blackboard.

ANSWER:

The ER diagram of the given scenario is given below. It consists of 3 entities connected via 2 relationships. Each entity has certain set of attribute and Primary key is mentioned as underlined attribute. Cardinality are shown

The data definition of the above ERD is given below. Five table are created from the given ERD. Tables are Student, Course, Teacher, Opt, Teaches.

DDL-

CREATE TABLE Student

(

StuID INT,

Name VARCHAR(20),

Address VARCHAR(100),

Phone VARCHAR(10),

DOB DATE,

PRIMARY KEY (StuID)

);

CREATE TABLE Course

(

CourseID INT,

Title VARCHAR(25),

Description VARCHAR(50),

Duration INT,

PRIMARY KEY (CourseID)

);

CREATE TABLE Teacher

(

TeacherID INT,

Name VARCHAR(30),

DOB DATE,

Qualification VARCHAR(20),

PRIMARY KEY (TeacherID)

);

CREATE TABLE Opt

(

StuID INT,

CourseID INT,

Year DATE,

Grade INT,

PRIMARY KEY (StuID, CourseID),

FOREIGN KEY (StuID) REFERENCES Student (StuID),

FOREIGN KEY (CourseID) REFERENCES Course (CourseID)

);

CREATE TABLE Teaches

(

TeacherID INT,

CourseID INT,

Day CHAR(10),

Date DATE,

Time DATE,

PRIMARY KEY (TeacherID, CourseID),

FOREIGN KEY (TeacherID) REFERENCES Teacher (TeacherID),

FOREIGN KEY (CourseID) REFERENCES Course (CourseID)

);