Database Systems

Database Systems

Bsc. Computer Science

 

Student Names / Group

 

2015

 

 

Section 1: Background

 

Having an online programs management system for London University requires an effective, efficient and friendly interface and a back end database. This report presents the identification and design of database needs to meet the roles of student registration.

Database Objectives

Input Objectives

  1. To store information about the various users of the systems that include
  • Students
  • Teaching staff
  • Principal Managers
  1. To store information about the universities programs
  • Programs
  • Modules
  • To store information about student enrolment
  • Program enrolment
  • Module enrolment

Output Objectives

  1. To retrieve student details including personal information and program details
  2. To retrieve information about the programs, modules and department
  • To retrieve data about the teaching staff and principal managers
  1. To manipulate data to suit the needs of the different users of the systems as illustrated in the query section below

 

 

 

 

 

Section 2: UML ERD Diagram

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Section 3: Relational Schema

 

Students (Student_Id,First_Name, Middle_Name, Last_Name, Gender, Birth_Date)

Program_Enrolment (Student_Id, Program_Id, Start_Date, End_Date)

Programs (Program_Id, Program_Name, Type, Number_of_Modules, Program_Leader, Department)

Module_Enrolment (Module_Id, Student_Id, Start_Date, End_Date, Credit, Result_Classification)

Modules (Module_Id, Module_Name, Credit, Tutor, Rep_Id,Program_Id)

Department (Department_Id, Department_Name, Department_Head)

Teaching_Staff (Staff_Id, First_Name, Middle_Name, Last_Name, Speciality, Qualification, Award_Winning_Institution, Previous_Employment, Type, Department, Salary, Manager)

Principle_Managers (PM_Id, First_Name, Middle_Name, Last_Name, Speciality, Qualification, Award_Winning_Institution, Previous_Employment, Type, Department, Salary)

 

Section 4: Assumptions, integrity andreferential Integrity

 

Students

Attribute Data Type Constraint
Student_Id Integer Auto Increment
First_Name String Not Null, Alphabet
Middle_Name String Not Null, Alphabet
Last_Name String Not Null, Alphabet
Gender String “M” or “F”
Birth_Date Date  
Email String Contain “@”

 

Module Enrolment

Attribute Data Type Constraint
Student_Id Integer Not Null
Module_Id Integer Not Null
Start_Date Date Not Null
End_Date Date Null
Credit Integer <15
Result Classification String ‘F’, ‘P’, ‘M’, ‘D’,
‘NS’, ‘FX’

 

Staff

Attribute Data Type Constraint
Staff_Id Integer Auto increment
First_Name String Alphabet
Middle_Name String Alphabet
Last_Name String Alphabet
Specialty String  
Qualification String  
Award_Winning_Institution String  
Previous_Employment String  
Department Integer Not Null
Salary Integer > £10,000 , < £150,000
Manager Integer  

 

Program Enrolment

Attribute Data Type Constraint
Student_Id Integer Not Null
Program_Id Integer Not Null
Start_Date Date Not Null
End_Date Date Null, >Start_Date + 3
Graduation_Date Date >27-Oct-2015

 

Programs

Attribute Data Type Constraint
Program_Id Integer Auto increment
Program_Name String Not Null
Type String “Part time -6yrs”  or “Full time -4yrs”
Number_of_Modules Integer >6, <8
Porgram_Leader Integer Not Null
Department Integer Not Null
Credit Integer < 360

 

 

 

 

Section 5: Database and Table creation

 

CREATE TABLE Students

(

Student_Id                 number (5) NOT NULL,

First_Name                 varchar2 (15) NOT NULL,

Middle_Name                        varchar2 (15) NOT NULL,

Last_Name                 varchar2 (15) NOT NULL,

Gender                                   varchar2 (15) NOT NULL,

Birth_Date                 Date

CONSTRAINT Student_Id PRIMARY KEY (Student_Id )

)

 

CREATE TABLE Program_Enrolment

(

Student_Id     number (5),

Program_Id    number (5),

Start_Date      Date,

End_Date       Date,

CONSTRAINT PEnrolment_Id PRIMARY KEY (Student_Id, Program_Id )

)

CREATE TABLE Programs (

Program_Id    number (5),

Program_Name          varchar2 (50),

Type                Varchar2 (50),

Number_of_Modules number (2),

Program_Leader         number (5),

Department                number (5),

CONSTRAINT Program_Id PRIMARY KEY (Program_Id )

)

 

CREATE TABLE teaching Staff

(

Staff_Id          number (5),

First_Name     varchar2 (20),

Middle_Name            varchar2 (20),

Last_Name     varchar2 (20),

Specialtiy

 

CONSTRAINT PEnrolment_Id PRIMARY KEY (Student_Id, Program_Id )

)

 

 

Section 6: Sample Data

Students

Student_Id First_Name Middle_Name
     
     
     

 

Programs

Program_ID Program_Name Type Number of Modules Program Leader  
1 Bs Computer Science   38 1  

 

Section 7: Queries

 

Query 1: List all Student Details and order by student Id

This query assumes that details about the student’s identification, and module the results are required

SELECT Student_Id, First_Name, Middle_Name, Last_Name, Gender, Program_ID, Program_Name, Start_Date, End_Date

FROM Students INNER JOIN Program_EnrollmentONStudents.Student_ID= Program_Enrollment.StudentID INNER JOIN Programs ON Program_Enrollment.Program_ID = Programs.Program_ID

ORDER BY Student_Id

 

Query 2: List all student reps in all programs

SELECT Student_Id, First_Name, Middle_Name, Last_Name, Gender, Program_ID, Program_Name

FROM Modules LEFT JOIN Students ON Modules.Rep_ID =Students.Student_Id

ORDER BY Student_Id

 

Query 3: List the names of all students who are enrolled on the Bsc Computer Science and Doing the Database Module

SELECT Student_Id, First_Name, Middle_Name, Last_Name, Gender, Program_ID, Program_Name, Start_Date, End_Date, Module

FROM Students INNER JOIN Module_Enrollment ON Students.Student_ID= Module_Enrollment.StudentID INNER JOIN Modules ON Module_Enrolment.Module_ID=Modules.Module_ID INNER JOIN Programs ON Modules.Program_ID = Programs.Program_ID

WHERE Program_Enrollment.Name=”Bsc. Computer Science” and Modules.Module_Name=”Database Module”

ORDER BY Student_Id

 

Query 4: Find the students with minimum marks in a specific module, giving the name of the student and module.

This prompts the user to input the name of the module or module ID

SELECT Student_Id, First_Name, Middle_Name, Last_Name, Module_Id, Module_Name, MIN (Credit) As MINIMUM MARKS

FROM Students INNER JOIN Module_Enrollment ON Students.Student_ID= Module_Enrollment.StudentID INNER JOIN Modules ON Module_Enrolment.Module_ID=Modules.Module_ID INNER JOIN Programs ON Modules.Program_ID = Programs.Program_ID

WHERE Program_Enrollment.Name=”Bsc. Computer Science” and Modules.Module_ID=”Module ID”

ORDER BY Student_Id

 

Query 5: Give a count of the number of the number of students on a specific module that started on 1st September, 2015

SELECT COUNT (*) “Number of Students”

FROM Module_Enrolment

WHERE Modules.Module_ID=”Module ID” and Modules.Start_Date=”1/9/2015”

 

Query 6: Give total credits acquired by each student

SELECT Student_Id, First_Name, Middle_Name, Last_Name,  Program_ID, Program_Name, SUM (Module_Enrollment.Credit) AS “Program Credits”

FROM Students INNER JOIN Module_Enrolment ON Students.Studnet_Id=Module_Enrolment.Student_Id INNER JOIN Modules ON Module_Enrolment.Module_ID=Modules.Module_ID INNER JOIN Programs ON Modules.Program_ID = Programs.Program_ID

GROUP BY Students.Student_Id

ORDER BY Student_Id

 

Query 7:List the academic’s name and salary and their Principle manager’s
name and salary for all academic staff who earn more than their
managers.

 

List the academic’s name, salary, principle manager’s name, pm salay  for

Earn more than their managers

SELECT Staff.First_Name,Staff.Middle_Name, Staff.Last_Name, Staff.Salary, , pm salary

FROM staff INNER JOIN manager ON Staff.pm=Managers.Staff ID

WHERE Staff.Salary>Staff.PrincpleSAlry

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: