A database design for a new registration system. The following requirements are
ID: 3731745 • Letter: A
Question
A database design for a new registration system. The following requirements are for designing a Database Schema.
1. College has several departments
2. Department offer several courses
3. Courses have sections.
4. College has locations (Room Sem 5 etc.)
5. Students register for a section from a catalog (which is composed of Departments, Courses, Sections, Location.)
6. Students have to pay for the course they take. You may want to think about resident, non resident.
7. Their status is updated based on their registration.
A document to help you remind the symbols for DB Schema is posted in the notes area for Session 1 - called "Introduction to RDBMS.doc".
Entity/Attributes
[PARENT]
User - id(pk), Firstname, Lastname, Address, Email, Telno, ResidentId, Type
[PARENT]
Location - id(pk), Location Name
[PARENT]
Payment - id(pk), PaymentName //Payment - visa/mc/cash - optional
[PARENT]
Resident - id(pk), ResiName, Feeschedule //Resident/Non-Resident
[PARENT]
College id(pk),Name, Address, Telno
[CHILD of College]
Department - id(pk), Name, Locid, Collegeid(fk)
[CHILD of Department]
Course - id(pk), CourseNo, CourseName, Prereq, Deptid(fk)
[CHILD of Course, User(instructor) and Location]
Section - id(pk), SectionNo, Courseid(fk), Locationid(fk), starttime, endtime, startdate, enddate, userid(fk)
[Child of User, Section, Payment]
StudentRegistration - id(pk), StudentID(fk), SectionId(fk)_, FeeStatus, Paymentid(fk)
//student-section
Catalog - view (query - Department, Course, Section, Location)
Relationships
1. College(parent) can have zero or more department(child)
2. Department(parent) has zero or more courses
//Students complete the listing of the relationships
The database schema is as below:
Question:
1. Create Table and Tablespaces Q1. Based on the schema in Assignment 6, create tables with constraints. Consider using the following constraints as appropriate Primary Key, Foreign key, Unique, Null or Check.
2. Project which tables will increase quickly and implement partitioning types in at least 2 tables in your schema. Explain why these partitioning types would be useful in the context of your implementation.
Vertabeldo Resident User College UserlD FirstName LastName Address Email ResidentiD Telno Type PK ResidentiD ResidentName varchar(50) FeeSchedule varchar (50) int PK PK varchar(50) varchar(50) varchar(50) varchar(50) int int varchar(50) CollegelD int Name Address varchar(50) Telno varchar(50) int FK Payment PaymentlD PaymenName varchar(50) PK Catalog Department Course Section Location int int int Student Registration Section StudentRegistrationlD int StudentlD SectionID FeeStatus PaymentlD PK FK Fk SectionlD SectionNo LocationID CourselD StartTime EndTime StartDate EndDate UserlD int PK int nt FK int FK ime ime date date int FK Location int int varchar(50) int LocationID LocationName varchar(50) PK Fk Course Department CourselD CourseNo CourseName Prerequisite DepartmentlD PK int varchar(50) varchar(50) int DepartmentID Name LocationID CollegelD int PK int int FK int FK FKExplanation / Answer
CREATE TABLE Resident(
ResidentID INT NOT NULL PRIMARY KEY,
ResidentName VARCHAR(50),
FreeSchedule VARCHAR(50)
);
CREATE TABLE User(
UserID int NOT NULL PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
`Address` VARCHAR(50),
Email VARCHAR(50),
ResidentID INT,
Telno INT,
`Type` VARCHAR(50),
FOREIGN KEY(ResidentID) REFERENCES Resident(ResidentID)
);
CREATE TABLE Payment(
PaymentID INT NOT NULL PRIMARY KEY,
PaymentName VARCHAR(50)
);
CREATE TABLE Location(
LocationID int NOT NULL PRIMARY KEY,
LocationName VARCHAR(50)
);
CREATE TABLE College(
CollegeID int NOT NULL PRIMARY KEY,
`Name` VARCHAR(50),
`Address` VARCHAR(50),
Telno int
);
CREATE TABLE Department(
DepartmentID int NOT NULL PRIMARY KEY,
`Name` VARCHAR(50),
LocationID int,
CollegeID int,
FOREIGN KEY(LocationID) REFERENCES Location(LoationID),
FOREIGN KEY(CollegeID) REFERENCES College(CollegeID)
);
CREATE TABLE Course(
CourseID int NOT NULL PRIMARY KEY,
CourseNo int,
CourseName VARCHAR(50),
Prerequisite VARCHAR(50),
DepartmentID int,
FOREIGN KEY(DepartmentID) REFERENCES Department(DepartmentID)
);
CREATE TABLE Section(
SectionID int NOT NULL PRIMARY KEY,
SectionNo int,
LoationID int,
CourseID int,
startTime TIME,
EndTime TIME,
StartDate DATE,
EndDate DATE,
UserID int,
FOREIGN KEY(UserID) REFERENCES User(UserID),
FOREIGN KEY(LoationID) REFERENCES Location(LocationID),
FOREIGN KEY(CourseID) REFERENCES Course(CourseID)
);
CREATE TABLE StudentRegistration(
StrudentRegistrationID int PRIMARY KEY,
StudentID int,
SectionID int,
FeeStatus VARCHAR(50),
PaymentID int,
FOREIGN KEY(PaymentID) REFERENCES Payment(PaymentID),
FOREIGN KEY(StudentID) REFERENCES User(UserID),
FOREIGN KEY(SectionID) REFERENCES Section(SectionID)
);