consider the following set of requirements for a database that is used to keep track information about a university

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (1.11 MB, 22 trang )

Trang 1<div class="page_container" data-page="1">

Database Systems - DBI202

Teacher: VU THANH PHONG

Table of contents: Page

ASSIGNMENT REPORT

</div>Trang 2<div class="page_container" data-page="2">

TOPIC ……….3

I. Introduction to the problem ……….……3

1. Describe the problem ……….3

2. Management goals ……… 4

II. Entity Relationship Model ……….4

1. Identify entity sets, attributes ……….4

2. Make E/R diagram ………5

III. Data Requirements Specification ………. 6

1. Relational model ………6

2. Database Diagram ………15

3. Trigger – Procedure – SQL Query ………16

</div>Trang 3<div class="page_container" data-page="3">

TOPIC 3

Consider the following set of requirements for a databasethat is used to keep track information about a university.I. Introduction to the problem (write the user requirement).

1. Describe the problem.

A university has a need to build a database to manage professors and manage projects done by students:

- Professors have an SSN, a name, an age, a rank, and a research specialty. Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget. Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.).

- Each project is managed by one professor (known as the project’s principal investigator). Each project is worked on by one or more professors (known as the project’s co-investigators). Professors can manage and/or work on multiple projects. Each project is workedon by one or more graduate students (known as the project’s research assistants).

- When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects. - Departments have a department number, a department name, and a main office. Departments have a professor (known as the chairman) who runs the department. Professors work in one or more departments.

- Graduate students have one major department in whichthey are working on their degree.

</div>Trang 4<div class="page_container" data-page="4">

- Each graduate student has another, more senior graduate student who advises him or her on what courses to take. Design and draw an ER diagram that captures theinformation about the university. Use only the basic ER model here; that is, entities, relationships, and attributes. Be sure to indicate any key and participation constraints.

Professors: ID, Name, Age, Ranking, ResearchDepartments: ID, Name, Office.

Students: ID, Name, Age, Degree.

Projects: ID, Name, StartingDate, EndingDate,

- Relation between entity:

+ Professors run Department+ Professors work in Department+ Professors manage Project+ Professors work on Project

+ Professors supervise Project, Student+ Students work on Projects

</div>Trang 5<div class="page_container" data-page="5">

+ Students major Department

+ Senior Graduate introduce Graduate Students

2. Make E/R diagram.

</div>Trang 6<div class="page_container" data-page="6">

III. Data Requirements Specification (Data dictionary).1. Relational model (Converted from E/R model).

1.1 Table definitionsa. TABLE PROFESSORS

AttributesData typeDefaultCheckKey/Index/Constrains

PSSNVarchar(20)PSSN like ‘P%’Primary keyName Nvarchar(50

Research Degree =‘Master’ or

Example:

</div>Trang 7<div class="page_container" data-page="7">

Create table statement:

CREATE TABLE Professors(

PSSN varchar(20) CHECK(PSSN like 'P%'),Name nvarchar(50),

Age int CHECK(Age >= 22),Ranking int CHECK(Ranking > 0),

Research nvarchar(50) CHECK(Research = 'Master' or Research = 'Doctor'),

BudgetFloat0Budget >= 0

</div>Trang 8<div class="page_container" data-page="8">

PR01P01Mar Messenger2022-10-012022-11-100PR02P02Arrange Traffic Lights2022-10-012022-11-100PR03P02Timetable Scheduler2022-10-122022-11-070

PR05P05Elementary Japanese2022-10-012022-11-010

Create table statement:

CREATE TABLE Projects(

ProjectNumber varchar(20) CHECK(ProjectNumber like 'PR%'),

PSSN varchar(20) CHECK(PSSN like 'P%'), Name nvarchar(50),

StartingDate date, EndingDate date,

Budget float CHECK(Budget >= 0) DEFAULT 0, Primary key (ProjectNumber),

FOREIGN KEY (PSSN) REFERENCES Professors(PSSN) )

</div>Trang 9<div class="page_container" data-page="9">

c. TABLE DEPARTMENTSAttribute

FOREIGN KEY (PSSN)REFERENCESProfessors(PSSN)DNameNvarchar(50)

Create table statement:

CREATE TABLE Departments (

DNumber int CHECK(DNumber > 0),

PSSN varchar(20) CHECK(PSSN like 'P%'),DName nvarchar(50),

</div>Trang 10<div class="page_container" data-page="10">

Office nvarchar(50),Primary key (DNumber),

FOREIGN KEY (PSSN) REFERENCES Professors(PSSN) )

d. TABLE STUDENTS

sData typeDefaultCheckKey/Index/Constrains

FOREIGN KEY (DNumber)REFERENCESDepartments(DNumber)NameNvarchar(50)

</div>Trang 11<div class="page_container" data-page="11">

Create table statement:

CREATE TABLE Students (

SSN varchar(20) CHECK(SSN like 'SE%'), SupSSN varchar(20) CHECK(SupSSN like 'SE%'), DNumber int CHECK(DNumber > 0),

Name nvarchar(50), Age int CHECK(Age >= 18), Degree nvarchar(30), Primary key (SSN),

FOREIGN KEY (DNumber) REFERENCES Departments(DNumber), FOREIGN KEY (SupSSN) REFERENCES Students(SSN)

ProjectNumber like 'PR%'

Primary Key, FOREIGNKEY (ProjectNumber)

)SSN Varchar(20

SSN like 'SE%'

Primary Key, FOREIGNKEY (SSN) REFERENCES

</div>Trang 12<div class="page_container" data-page="12">

Create table statement:

CREATE TABLE WorkOnPRJS (

ProjectNumber varchar(20) CHECK(ProjectNumber like 'PR%'),

SSN varchar(20) CHECK(SSN like 'SE%'), Primary key (SSN, ProjectNumber), FOREIGN KEY (ProjectNumber) REFERENCES Projects(ProjectNumber),

FOREIGN KEY (SSN) REFERENCES Students(SSN))

f. TABLE WORKONPROS

AttributesData typeDefaul

PSSN Varchar(20)

PSSN like 'P%'

Primary Key, FOREIGNKEY (PSSN)REFERENCESProfessors(PSSN)ProjectNumbe

ProjectNumber like 'PR%'

Primary Key, FOREIGNKEY (ProjectNumber)

Example:

</div>Trang 13<div class="page_container" data-page="13">

Create table statement:

CREATE TABLE WorkOnPROS (

PSSN varchar(20) CHECK(PSSN like 'P%'),

ProjectNumber varchar(20) CHECK(ProjectNumber like 'PR%'),Primary key (PSSN, ProjectNumber),

FOREIGN KEY (PSSN) REFERENCES Professors(PSSN),FOREIGN KEY (ProjectNumber) REFERENCES

g. TABLE SUPERVISE

AttributesData typeDefaul

PSSN Varchar(20)

PSSN like 'P%'

Primary Key, FOREIGNKEY (PSSN) REFERENCES

ProjectNumber Varchar(20)

ProjectNumber like 'PR%'

Primary Key, FOREIGNKEY (ProjectNumber)

)SSN Varchar(20

SSN like 'SE%'

Primary Key, FOREIGNKEY (SSN) REFERENCES

Example:

</div>Trang 14<div class="page_container" data-page="14">

Create table statement:

CREATE TABLE Supervise (

PSSN varchar(20) CHECK(PSSN like 'P%'),

ProjectNumber varchar(20) CHECK(ProjectNumber like 'PR%'),SSN varchar(20) CHECK(SSN like 'SE%'),

Primary key (PSSN, SSN, ProjectNumber),

FOREIGN KEY (PSSN) REFERENCES Professors(PSSN),FOREIGN KEY (ProjectNumber) REFERENCES

Primary Key, FOREIGNKEY (DNumber)

Example:

</div>Trang 15<div class="page_container" data-page="15">

Create table statement:

CREATE TABLE WorkIn (

PSSN varchar(20) CHECK(PSSN like 'P%'),DNumber int CHECK(DNumber > 0),

Hourss int CHECK(Hourss >= 0) DEFAULT 0,Primary key (PSSN, DNumber),

FOREIGN KEY (PSSN) REFERENCES Professors(PSSN),FOREIGN KEY (DNumber) REFERENCES Departments(DNumber))

2. Database diagram

</div>Trang 16<div class="page_container" data-page="16">

3. Trigger – Procedure – SQL Query1. SQL QUERY

a. Display all students that not join in any projectsQuery Statement:

SELECT .SSN, .SupSSN, .DNumber, .Name, .Age, .DegreeFROM STUDENTS S LEFT JOIN WORKONPRJS W ON .SSN = W SSNLEFT JOIN PROJECTS P ON .ProjectNumber = P ProjectNumberWHERE .ProjectNumber IS NULL

b. Display the number of professor that is working in each department.

Query Statement:

SELECT .DNumber, .DName, COUNT( .P PSSN) AS NumOfProfessor

</div>Trang 17<div class="page_container" data-page="17">

FROM Departments D INNER JOIN WorkIn WI ON .DNumberWI DNumber.

INNER JOIN Professors P ON WI.PSSN = P PSSNGROUP BY .DNumber, .DName

c. Display all students that doesn’t have supervise studentQuery Statement:

SELECT SSN, DNumber, Name, Age, DegreeFROM Students

WHERE SupSSN is null

2. TRIGGER

a. Trigger for insert on Projects table

Usage: If the row that user insert into Projects table has

Ending Date that not after the Starting Date. The trigger will print the notification and rollback for avoiding this insert. Otherwise, the row that user insert will be inserted normally.

Create Trigger Statement:

CREATE TRIGGERstartDate_endDate_checkonProjects

FOR INSERTASBEGIN

DECLARE@SDATEDATE,@EDATEDATE

</div>Trang 18<div class="page_container" data-page="18">

SELECT@SDATE =StartingDate,@EDATE =EndingDate

SELECT@SYEAR =YEAR(@SDATE),@SMONTH =MONTH(@SDATE),@SDAY

SELECT@EYEAR =YEAR(@EDATE),@EMONTH =MONTH(@EDATE),@EDAY

IF (@EYEAR <@SYEAR)BEGIN

PRINT('The ending date need to be after the starting date')ROLLBACK TRAN

IF (@EMONTH <@SMONTH)BEGIN

PRINT('The ending date need to be after the starting date')

ROLLBACK TRANEND

IF (@EDAY <@SDAY)BEGIN

PRINT('The ending date need to be after the starting date')

ROLLBACK TRANEND

SELECT *FROMINSERTED

b. Trigger for insert on Students table

Usage: If the row that user insert into Students table has

Supervise Student that has degree is not Senior Graduate. The trigger will print the notification and rollback for avoiding

</div>Trang 19<div class="page_container" data-page="19">

this insert. Otherwise, the row that user insert will be inserted normally.

Create Trigger Statement:

CREATE TRIGGERSUP_STU_TRIGONSTUDENTS

FOR INSERTASBEGIN

DECLARE@SUP_SSNVARCHAR(20)SELECT@SUP_SSN =SupSSN

SELECT *FROMINSERTED

c. Trigger for insert on WorkIn table

Usage: The professor that manage the department need to have

the highest ranking (Rank 1 > Rank 2) in department. If the row that user insert into WorkIn table has professor has higher ranking than department's manager. The trigger will print the notification and rollback for avoiding this insert.Otherwise, the row that user insert will be inserted

Create Trigger Statement:

CREATE TRIGGERcheck_WorkInONWORKIN

FOR INSERTASBEGIN

</div>Trang 20<div class="page_container" data-page="20">

DECLARE@DNUMINT,@IPSSNVARCHAR(20)SELECT@DNUM =DNUMBER,@IPSSN =PSSN

DECLARE@ManagerNumVARCHAR(20)SELECT@ManagerNum =PSSN

WHEREDNumber =@DNUM

DECLARE@IRANKINGINTSELECT@IRANKING =Ranking

PRINT('The manager need to have highest ranking in the department!')

ROLLBACK TRANEND

SELECT *FROMINSERTED

2. STORE PROCEDURE

a. Procedure that helping user delete a project by the Project Number

Usage: Normally, if user want to delete a Project they need

to delete in all table that referenced to Projects table. Therefore, we create this procedure that help user can deletea project by the Project Number in one statement.

Create procedure statement:

CREATE PROCEDUREDELETE_PRO_BY_KEY @PRONUMvarchar(20)AS

DELETE FROMWorkOnPROS

</div>Trang 21<div class="page_container" data-page="21">

WHEREProjectNumber =@PRONUM

DELETE FROMSupervise

WHEREProjectNumber =@PRONUM

DELETE FROMWorkOnPRJS

WHEREProjectNumber =@PRONUM

DELETE FROMProjects

WHEREProjectNumber =@PRONUM

b. Procedure that helping user Insert a project into the Projects table

Usage: Normally, if user want to insert a Project they need

to insert the professor if PSSN not exist in the Professor table. Therefore, we create this procedure that help user caninsert a project into Projects table in one statement. At thesame time, we put the insert statement in TRANSACTION and in block TRY so if any error happen the insert will not be inserted to table for avoiding wrong data in database.

Create procedure statement:

CREATE PROCEDUREINSERT_PRO

@projNumvarchar(20), @PSSNvarchar(20), @projNamenvarchar(50), @startDateDate, @endDateDate, @budgetint,

@profNamenvarchar(50), @profAgeint,

@rankingint,

@researchnvarchar(50)AS

BEGIN

</div>Trang 22<div class="page_container" data-page="22">

GROUP BYPSSN

HAVINGPSSN =@PSSN

IF (@CHECKisnotnull)BEGIN

INSERT INTOProjects