2004 October Lab Project

Information & Computer Science Department

King Fahd University of Petroleum & Minerals

Course Number ICS 334

Course Title DATABASE SYSTEMS

Time Allowed Hand-out Date: 17-OCT-2004 Due Date: 29-DEC-2004

Duration

Instruction to Candidates:

This home work involves practice using Oracle SQL.

Answer ALL questions, with print output using font “Courier New size 10” with cover sheet.

Group Assignment: Maximum students in a group are THREE.

Submission:

Late homework will not be accepted.

Copying home work or source code will result an F for the course (I will verify in this home work very strictly, so don’t copy otherwise both party will get zero)

______

Suggestion: Start work on this assignment as soon as possible after hand in date that helps to answer or solve it easily with perfect results. It may be possible results or answers may not be perfect if trying to work on in last few days before submission. Marks will be deducted on wrong submission or on any discrepancy against defined standards.

Objective:

Enable the student to design and develop a database. In this project, the students are required to show their abilities of:

§  Analyzing the System Requirement

§  Represent the requirement into logical design using Entity Relationship (ER) or/and Enhanced Entity Relationship (EER) model

§  Mapping the designed model into relational schemas

§  Transform the relational schemas into normalized tables (Normalization)

§  Writing SQL statements to creates the tables including all applicable integrity constraints

§  Writing SQL statements to populate the initial records of each table

Case Brief

BIIT Student’s Information System

1. Introduction

Barani Institute of Information Technology (BIIT) introduced a project to automate its student information system. The partial design of the project is completed and the institute needs to implement the system with the given screens layouts and system design. Every year applicants apply for an admission in degree courses like BCS, MCS, MS etc. Software must keep complete personal information of each student who gets an admission. Software is keeping complete records of courses offered in different levels and personal information of faculty members. Courses (with multiple sections in evening & morning) are offered in every semester along with name of faculty members who teach course(s). It is possible one or different faculty members teach more same/ different courses. Sometimes, courses cannot be offered in a semester because of lake of facilities. Software will keep track record of all courses details with student’s results and other related information such that various reports can be generated to get all possible information. It is necessary to keep information related to semester’s start date and end date, fee deposited by student for a semester and course grades (like marks, grades, status etc.).

Using this system requires sophisticated security and audit facilities such that only valid users can open screens; some users can have access like read, write and update (or combination of these rights). Valid messages and instructions are important so that user gets a various reasons regarding their privileges. Database must keep track record of each record such that who and when has manipulated.

Some of the information can be obtained from given screens to design a database and some of them can be discussed with your lab instructor.

Necessary documentation includes format database’s objects like tables, attributes and constraints. The following sample can be adopted for tables.

TableName

Aggregate score table.

Sr. # / Field Name / Description / Datatype / Key / Validation
1 /

CNo

/ Course Number, as defined in master index(CRSMTR) / CHAR(6) / P/F / Example: CS556


System’s Context Diagram:


Prototyping Screens’ Layouts

Following are the screen layouts that are required in the client programming interface.




Similar screen can be develop using only Reg. number (ignore App. No).

Some of the following column names are required which are also shown in above screens.

Reg_no,

firstname,

lastname,

middlename,

email,

Sex,

Marital_status,

Birth_date,

Citizenship no,

Address,

City,

Telno,

Home telno,

Status,

Session,

Admission date,

Semester no,

Remarks,

St_status,

User_id,

Final_course,

Discipline

Attempt no

Course no

Final score

Course desc

Course type

Midterm score

Ass score

Credit hrs

Prac score

Grade

Semester desc

Sem Start date

Sem End date

Q points

User id

Emp no

Emp firstname

Emp lastname

Emp middle

Dsgn no

Per address

Per city

Pr address

Pr city

Res telno

Emp email

Joining date

Resign date

Max score

Obt score

Dsgn no

Job title

Job resp

Job cat

Submission:

Document inclusive of following items in appropriate format

§  ER and/or EER diagram

§  Relational schemas

§  Description of all tables

§  SQL statements used to create tables and initial instances.

§  SQL statements as the answer to the needed 12 queries

§  Use of Case tool is must for this project

Grade distribution:

Items / % / Submission Date
ER and/or EER diagram / 20 / 28-NOV-2004, sec#52, 53
Relational Schemas / 10 / 05-DEC-2004, sec#52, 53 (8:00pm 22/124-6)
Tables’ description / 10 / 05-DEC-2004, sec#52, 53 (8:00pm 22/124-6)
SQL statements:
-  creating tables
-  answering the needed queries
Normalization
Screen Layouts (Executable with DB connection) / 40 / 19-DEC-2004, sec#52, 53 (8:00pm 22/124-6)
Presentation/ Demo (Final product) / 20 / 22-DEC-2004 sec#53 (22/336)
26-DEC-2004 sec#52 (22/336)
T O T A L / 100


College of Computers Science and Engineering

Information and Computer Science Department

HOME WORK ASSIGNMENT COVER SHEET

COURSE TITLE DATABASE SYSTEMS COURSE CODE ICS334Lab

INSTRUCTOR EJAZ AHMED TYPE Ö (Please tick)

Indiv. Group

ASSIGNMENT NO 1

ASSIGNMENT Project SysArid (Arid Student Information System)

HAND OUT DATE 13-OCT-2004 DUE DATE 14-DEC-2004

ESTIMATE TIME 30 Hours

ASSESSMENT CRITERIA (or attached) /
% Mark
Data Modeling (ER/ EER Design)
Database Design
Presentation, Normalization, Table or Schema design, Scripts, Procedures, Triggers
User Interfaces (GUI)
Presentation, interfaces/ reports using Java frames or any other appropriate application
software
TO BE COMPLETED BY STUDENT GROUP MEMBERS ID

NAME

ID NO
Time Taken:
DECLARATION: I/We declare that this Coursework is my/our group’s own work
SIGNATURES (All members)

GRADE/ MARK AWARDED COMMENTS

INSTRUCTOR’S SIGNATURE DATE