SIT103: Database Concepts Assignment - Database Design and SQL

This assignment focuses on database concepts, encompassing the design and implementation of a relational database for a video game center. It begins with defining business rules that govern the database's structure and functionality, ensuring it effectively tracks student, course, faculty, and department details. An Entity-Relationship (ER) diagram visually represents the database's structure, outlining entities such as students, grants, deans, colleges, departments, registration, courses, and staff, along with their attributes and relationships. The assignment then provides a relational schema detailing the tables and their attributes, including primary and foreign keys. SQL commands are presented for creating tables, inserting data, updating records, and querying the database to retrieve specific information, such as students within a certain age range, or departments without any courses. The solution also includes a bibliography of relevant database systems resources.

Running head: DATABASE CONCEPTS
Database Concepts
Name of the Student:
Name of the University:
Author Note

1
DATABASE CONCEPTS
Business Rules
The business rules for the video game centre database is provided below. These
business rules are very important for developing the database.
 The database should be helpful for the university in keeping track of the details of the
students, their majors and transcripts and registration details as well.
 The database should store the details of the courses, which are offered by the
university. These courses are to identified by their unique course ID.
 The details of the faculties and the students in the universities are to be stored in the
system and their student ID and Faculty ID. Indexes the details.
 The details of each of the departments identified by their department ID is to be stored
in the database.
 The details of each of the colleges are also required to be stored in the system that
would be related to the dean of the college.
 In addition to all this, the database should store the details of the grants for each of the
college. The grants are identified by their unique grants number and the should
provide the information about the starting date of the grant. A grant has a relation to a
single principle investigator.
 A separate entity for database has been created for the Deans in the colleges, which
are identified by their unique Dean ID number.

2
DATABASE CONCEPTS
ER Diagram
Relational Schema
Student (SID: int, StudentName: varchar (50), Age: Number, Address: varchar (50))

3
DATABASE CONCEPTS
Grants (GrantNumber: int (pk), GrantTitle: varchar(50), AwardingAgency: varchar(50),
StartingDate: date)
Dean (DeanID: int (pk), DeanName: varchar (50))
College (OfficeNumber: int (pk), CollegaName: varchar(50), DeanID: int (fk), GrantNumber:
int (fk))
Department (DepartmentID: int (pk), MajorName: varchar (50), DeanID: int (fk))
Registration (RegistrationID: int (pk), SID: int (fk), SocialSecurityNumber: int (fk),
DepartmentID: int (fk))
Course (CourseNumber: int (pk), CourseName: varchar (50), CourseDesc: varchar (100),
DepartmentID: int (fk))
Staff (SocialSecurityNumber: int (pk), StaffName: varchar(50), Address: varchar (100),
DOB: date, Rank: varchar(50), OfficePhone: number, AnuualSalary: Number,
DepartmentID: int (fk))
SQL Commands
--Query 1
CREATE TABLE STUDENT(
SID int NOT NULL,
StudentName varchar(50) NOT NULL,
Age number NOT NULL,
Address varchar(40) NOT NULL,

4
DATABASE CONCEPTS
CONSTRAINT PK_student PRIMARY KEY (SID)
);
CREATE TABLE GRANTS(
GrantNumber int NOT NULL,
GrantTitle varchar(50) NOT NULL,
AwardingAgency varchar(50) NOT NULL,
StartingDate date NOT NULL,
CONSTRAINT PK_grants PRIMARY KEY (GrantNumber)
);
CREATE TABLE DEAN(
DeanID int NOT NULL,
DeanName varchar(50) NOT NULL,
CONSTRAINT PK_dean PRIMARY KEY (DeanID)
);

5
DATABASE CONCEPTS
CREATE TABLE DEPARTMENT(
DepartmentID int NOT NULL,
MajorName varchar(50) NOT NULL,
DeanID int NOT NULL,
CONSTRAINT PK_dept PRIMARY KEY (DepartmentID),
CONSTRAINT FK_dept FOREIGN KEY (DeanID) REFERENCES Dean(DeanID)
);
CREATE TABLE COURSE(
CourseNumber int NOT NULL,
CourseName varchar(50) NOT NULL,
CourseDesc varchar(100) NOT NULL,
DepartmentID int NOT NULL,
CONSTRAINT PK_course PRIMARY KEY (CourseNumber),
CONSTRAINT FK_course FOREIGN KEY (DepartmentID) REFERENCES Department
(DepartmentID)

6
DATABASE CONCEPTS
);
CREATE TABLE STAFF(
SocialSecuityNumber int NOT NULL,
StaffName varchar(50) NOT NULL,
Address varchar(100) NOT NULL,
DOB date NOT NULL,
Ranks varchar(50) NOT NULL,
OfficeNumber number NOT NULL,
AnualSalary number NOT NULL,
DepartmentID int NOT NULL,
CONSTRAINT PK_staff PRIMARY KEY (SocialSecuityNumber),
CONSTRAINT FK_staff FOREIGN KEY (DepartmentID) REFERENCES
Department(DepartmentID)
);

7
DATABASE CONCEPTS
CREATE TABLE COLLEGE(
OfficeNumber int NOT NULL,
CollegeName varchar(50) NOT NULL,
DeanID int NOT NULL,
GrantNumber int NOT NULL,
CONSTRAINT PK_college PRIMARY KEY (OfficeNumber),
CONSTRAINT FK1_college FOREIGN KEY (DeanID) REFERENCES Dean(DeanID),
CONSTRAINT FK2_college FOREIGN KEY (GrantNumber) REFERENCES
Grants(GrantNumber)
);
CREATE TABLE REGISTRATION(
RegistrationID int NOT NULL,
SID int NOT NULL,

8
DATABASE CONCEPTS
SocialSecuityNumber int NOT NULL,
DepartmentID int NOT NULL,
CONSTRAINT PK_reg PRIMARY KEY (RegistrationID),
CONSTRAINT FK1_reg FOREIGN KEY (SID) REFERENCES Student(SID),
CONSTRAINT FK2_reg FOREIGN KEY (SocialSecuityNumber) REFERENCES
Staff(SocialSecuityNumber),
CONSTRAINT FK3_reg FOREIGN KEY (DepartmentID) REFERENCES
Department(DepartmentID)
);
-- Query 2
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(1,'Tom Hardy',19,'23
Downtown Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(2,'Kevin
Spacey',18,'4 Southend Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(3,'Richard
Brett',19,'5 Northend Street');

9
DATABASE CONCEPTS
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(4,'Harry Levin',18,'7
Wellington Road');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(5,'Kevin
Friend',17,'10 Princeton Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(6,'Tom Clacy',18,'6
Southend Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(7,'Jerry Smith',20,'23
Princeton Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(8,'Harvey
Spectre',18,'45 Downtown Street');
INSERT INTO STUDENT (SID,StudentName,SeAge,Address) VALUES(9,'Jerry
Crowe',18,'12 Downtown Street');
INSERT INTO STUDENT (SID,StudentName,Age,Address) VALUES(10,'Jimmy
Smith',19,'23 Wellington Road');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(1,'Academic Competitiveness Grant','Clifford Rogers','23/Jan/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(2,'Pell Grant','Leonard Capri','21/Jun/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(3,'Women Grant','Jerry Rogers','05/Jan/1997');

10
DATABASE CONCEPTS
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(4,'College Grant for latino','Clifford Rogers','23/Aug/1998');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(5,'College Grant for women','Leonard Capri','23/Feb/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(6,'College Grant for Physically challenged','Jerry Rogers','23/Jun/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(7,'College Grant for Europeans','Clifford Rogers','23/Dec/1995');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(8,'College Grant for Africans','Jerry Rogers','20/Dec/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(9,'College Grant for Americans','Leonard Capri','12/Jan/1993');
INSERT INTO GRANTS (GrantNumber,GrantTitle,AwardingAgency,StartingDate)
VALUES(10,'College Grant for Europeans','Jerry Rogers','11/Jan/1993');
INSERT INTO DEAN (DeanID,DeanName) VALUES(1,'Dr.Trelos');
INSERT INTO DEAN (DeanID,DeanName) VALUES(2,'Dr.Philip');
INSERT INTO DEAN (DeanID,DeanName) VALUES(3,'Dr.Zues');
INSERT INTO DEAN (DeanID,DeanName) VALUES(4,'Dr.Smith');
INSERT INTO DEAN (DeanID,DeanName) VALUES(5,'Dr.Williams');
INSERT INTO DEAN (DeanID,DeanName) VALUES(6,'Dr.Davids');

11
DATABASE CONCEPTS
INSERT INTO DEAN (DeanID,DeanName) VALUES(7,'Dr.Strellers');
INSERT INTO DEAN (DeanID,DeanName) VALUES(8,'Dr.Friend');
INSERT INTO DEAN (DeanID,DeanName) VALUES(9,'Dr.Trinckers');
INSERT INTO DEAN (DeanID,DeanName) VALUES(10,'Dr.Rubio');
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(1,'Princetop College', 1, 1);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(2,'Wellingtonlington College', 2, 2);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(3,'Southend College', 3, 2);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(4,'Northend College', 4, 1);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(5,'Stuart Spring College', 5, 10);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(6,'Harvey david College', 6, 9);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(7,'Phillip College', 7, 7);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(8,'St.James College', 8, 4);

12
DATABASE CONCEPTS
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(9,'Infotech College', 9, 3);
INSERT INTO COLLEGE (OfficeNumber,CollegeName,DeanID,GrantNumber)
VALUES(10,'Grance Williams College', 10, 1);
INSERT INTO DEPARTMENT (DepartmentID, MajorName, DeanID)
VALUES(1,'Computer Science', 4);
INSERT INTO DEPARTMENT (DepartmentID, MajorName, DeanID)
VALUES(2,'Infromation Technology', 2);
INSERT INTO DEPARTMENT (DepartmentID, MajorName, DeanID)
VALUES(3,'Accounts', 3);
INSERT INTO DEPARTMENT (DepartmentID, MajorName, DeanID) VALUES(4,'Cloud
Analytics', 6);
INSERT INTO DEPARTMENT (DepartmentID, MajorName, DeanID) VALUES(5,'Internet
technologies', 5);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(1,'Computer Organization','Information about computers', 1);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(2,'IT','Information about technologies', 2);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(3,'Computer Architecture','Information about computers', 1);

13
DATABASE CONCEPTS
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(4,'Pipelining Technologies','Information about computers', 1);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(5,'SPSS','Information about Accounts', 3);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(6,'Organization Analysis','Information about Cloud technologies', 4);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(7,'Java','Information about computers', 1);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(8,'ANOVA','Information about Accounts', 3);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(9,'Internet technologies','Information about technology', 2);
INSERT INTO COURSE (CourseNumber, CourseName, CourseDesc, DepartmentID)
VALUES(10,'DBMS','Information about computers', 1);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(1,'Mr.Benson','43 Greenwich road', '12/Jan/1993','Assitant',686788,7000,1);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(2,'Mr.Kevin','89 Wellington road', '12/Jun/1995','Assitant',6889788,6700,1);

14
DATABASE CONCEPTS
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(3,'Mr.Galloway','51 Southend road', '12/Jul/1993','Assitant',689088,7000,2);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(4,'Mr.Friend','66 Northend road', '12/Jan/1993','Assitant',686709,5600,3);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(5,'Mr.Friend','99 Greenwich road', '12/Aug/1991','Assitant',686098,5000,5);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(6,'Mr.Williams','40 Sothend road', '12/Jan/1997','Assitant',686678,7000,1);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(7,'Mr.Davids','48 Northend road', '12/Feb/1993','Assitant',686709,7500,1);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(8,'Mr.Pearson','49 Greenwich road',
'12/Jul/1996','Assitant',686710,17000,4);
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(9,'Mr.Spearce','25 Central road', '12/Dec/1993','Assitant',686700,9000,1);

15
DATABASE CONCEPTS
INSERT INTO STAFF
(SocialSecuityNumber,StaffName,Address,DOB,Ranks,OfficeNumber,AnualSalary,Departm
entID) VALUES(10,'Mr.Grewal','21 Greenwich road',
'12/Jan/1994','Assitant',686900,8000,5);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(1,1,1,1);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(2,2,3,1);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(3,3,4,2);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(4,4,5,3);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(5,5,1,2);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(6,6,2,4);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(7,7,3,5);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(8,8,1,2);

16
DATABASE CONCEPTS
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(9,9,4,3);
INSERT INTO REGISTRATION (RegistrationID, SID, SocialSecurityNumber,
DepartmentID) VALUES(10,10,3,1);
--Query 3
UPDATE STAFF
SET AnuualSalary = AnuualSalary * 1.05;
--Query 4
SELECT COURSE.CourseNumber, COURSE.CourseName
FROM COURSE INNER JOIN DEPARTMENT ON COURSE.DepartmentID =
DEPARTMENT.DepartmentID
WHERE DEPARTMENT.DepartmentName = 'Computer Science';
--Query 5
SELECT * FROM STUDENT WHERE AGE < 21 AND AGE > 18;

17
DATABASE CONCEPTS
--Querty 6
List the names of the departments that do not have any courses under them.
SELECT * FROM DEPARTMENT WHERE DEPARTMENTID NOT IN (SELECT
DEPARTMENTID FROM COURSE);

18
DATABASE CONCEPTS
Bibliography
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Hingorani, K., Gittens, D. and Edwards, N., 2017. REINFORCING DATABASE
CONCEPTS BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND
NORMALIZATION TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in
Information Systems, 18(1).