Assignment Top Sheet
Department of Computer Science
Class Section Semester
Student Registration Number
MCS-013R16-19
MCS A 3rd
Course Name Course Code
Advance Database System CS514
Student's Full Name
Sobia Yasseen
Assign Date: Submission Deadline:
11-10-2017 04-11-2017
Course Leader Name Course Coordinator Name
Sir.Nounam Farooq Ma’am Ghazala
Assignment No.: Assignment Details:
1
Instructions to Student:
Please Note: Work presented in an assessment must be your own. Plagiarism is
where a student copies work from another source, published or unpublished
(including the work of another student) and fails to acknowledge the influence of
another’s work or to attribute quotes to the author. Plagiarism is an academic offence
and the penalty can be serious. To detect possible plagiarism we may submit your
work to the national plagiarism detection facility. This searches the Internet and an
extensive database of reference material including other students’ work to identify.
I have read the above information and I confirm that this work is my
own and that it may be processed and stored in the manner described.
Signature:.............Sobia....................................................... Date: .......04-11-2017
Please leave sufficient time to meet the given deadline and do not leave the handing-in of assignments
to the last minute. You are being allowed meaningfull time for any system problems or other issues.
Lab Assignment No. 01:
Scenario-1:
A person must be a citizen of at most one country. A country must
have one or more than one citizens.
Entities:
o PERSON
o COUNTRY
Logical reasoning:
A PERSON must belong to a COUNTRY:
Ali is the citizen of Pakistan. John is the citizen of America. Ali does not be
the citizen of Pakistan and America at a time and John does not be the citizen
of Pakistan and America at a time.
P1 C1
P2 C2
P3 C3
A COUNTRY must have more than one PERSON:
Ali is the citizen of Pakistan. Ahmed and Ayesha are also the citizen of the
Pakistan.
P1 C1
P2 C2
P3 C3
ERD
Attributes:
countryId:
key,simple,single-valued,stored,required
countryName
Non-key,simple,single valued,stored,required
personCNIC
key,simple,single valued,stored,required
personFname
Non-key,simple,single valued,stored,required
personLname
Non-key,simple,single valued,stored,required
Scenario-2:
In a school, Each STUDENT must be allocated to exactly one class and a
CLASS must be formed by at least twenty or more than twenty number of
STUDENTs. There are Many Mandatory posts for STUDENT’s exists in every
CLASS and there are Many Mandatory STUDENT’s assigned to different
posts (like CR and GR) in every CLASS.
Logical reasoning
Each STUDENT must be allocated to exactly one class and a CLASS
must be formed by at least twenty or more than twenty number of
STUDENTs.:
Sobia id the STUDENT of CLASS M.C.S.She could not be a
STUDENT of M.C.S and M.I.T at a time.
Sidra is the STUDENT of M.C.S. Sobia and Shazia are also the STUDENT of
M.C.S.
C1 S1 C1 S1
C2 S2 C2 S2
C3 S3 C3 S3
ERD:
There are Many Mandatory posts for STUDENT’s exists in every CLASS
A CLASS M.C.S has two POST like C.R and G.R. CLASS M.i.T also has the
POST of C.R and G.R
C1 C.R C1 C.R
C2 G.R C2 G.R
ERD:
There is many to many relation therefor junction table\central table will be
formed
:There are Many Mandatory STUDENT’s assigned to different posts (like
CR and GR) in every CLASS
Sobia is the G.R of M.C.S and Asad is the C.R of M.C.S.
S1 C.R S1 C.R
S2 G.R S2 G.r
ERD:
COMPLETE ERD:
Attributes:
stdId:
key,simple,single-valued,stored,required
classId:
key,simple,single-valued,stored,required
stdFname
Non-key,simple,single valued,stored,required
stdLname
Non-key,simple,single valued,stored,required
post
non key,simple,single-valued,stored,required
program
Non-key,simple,single valued,stored,required
session
Non-key,simple,single valued,stored,required
Code
drop database if exists `lab1solution`;
create database if not exists `lab1solution`;
use `lab1solution`;
create table `lab1solution`.`country`
(
countryId varchar(10) primary key,
countryName varchar(20) not null
);
/*Create table country in citizen database
along with attributes */
create table `lab1solution`.`person`
(
personCNIC varchar(20) primary key,
personFname varchar(20) not null,
personLname varchar(20) not null,
countryId varchar(10)
);
/*Create table person in citizen database
along with attributes */
Alter table `lab1solution`.`person` add constraint fk_countryId foreign
key (countryId) references country(countryId);
/*drop database if exists `lab1solution`;
create database if not exists `lab1solution`;
use `lab1solution`;
create table if not exists `lab1solution`.`class`
(
classId tinyint primary key,
--primary key (classId),
Program varchar(10) not null,
Session varchar(20) not null
);
Create table if not exists `lab1solution`.`student`
(
stdId varchar(20) primary key,
--primary key(stdId),
stdFname varchar(20) not null,
stdLname varchar(20) not null,
classId tinyint not null
);
Create table if not exists `lab1solution`.`post`
(
stdId varchar(20) not null,
classId tinyint not null,
postTitle varchar(10) not null
);
Alter table `lab1solution`.`post` add constraint `fk_Post_student` foreign
key(stdId) references student(stdId);
Alter table `lab1solution`.`post` add constraint `fk_ post_class` foreign
key(classId) references class(classId);
Alter table `lab1solution`.`student` add constraint `fk_ student_class` foreign
key(classId) references class(classId);
*/
Class diagram
