ec601-lab3

EC601 Database System

DEPARTMENT OF ELECTRICAL ENGINEERINGEC601 – DATABASE SYSTEM

SESSION: JUNE 2013 Program: DTK 6LAB 3: RELATIONAL DATA MODEL

Duration: 2 Hours

NAME ID NO MARKS1.2.

Learning OutcomesBy the end of this laboratory session, students should be able to:Apply structured query language (SQL) for database manipulation using a database management system in practical works. (P4).

Objectives:1. Define and apply the fundamental operators from relational databases.2. Use the operators.

Hardware/ Software : Personal Computer / Program Application (Microsoft Access 2007)

PRACTICAL WORK ASSESSMENT RUBRICS

CATEGORY SKILLS STUDENT 1 STUDENT 2Psychomotor (80%)

1. Preparedness /20 /20

2. Operation /20 /20

3. Following Procedure /20 /20

4. Practical Work Finding /20 /20

Cognitive (20%)

1. Analysis and Theory Application /5 /5

2. Teamwork Skills /5 /5

3. Discussion /5 /5

4. Conclusion /5 /5

TOTAL (CLO 2) /100 /100


CATEGORY PSYCHOMOTOR

17 – 20 13 – 16 9 - 12 5 - 8 1 - 4

Preparedness

Brings needed materials to class and is always ready to work.

While conducting the procedure, the student is: tidy, respectful of others, mindful of safety and leaves the area clean.

Almost always brings needed materials to class and is ready to work.

While conducting the procedure, the student is: tidy, respectful of others, mindful of safety and leaves the area clean.

Almost always brings needed materials but sometimes needs to settle down and get to work

While conducting the procedure, the student is: mostly tidy, sometimes respectful of others, sometimes mindful of safety, and leaves the area clean only after

being reminded.

Sometimes always brings needed materials but needs to settle down and get to work.

While conducting the procedure, the student is: not always tidy, sometimes respectful of others, sometimes mindful of safety, and leaves the area clean only after

being reminded.

Often forgets needed materials or is rarely ready to get to work.

While conducting the procedure, the student is: untidy, not respectful of others, not mindful of safety, and leaves the area messy even after

being reminded.

Operation

Effectively understand and interpret information in lab sheet

The question to be answered during the lab is clearly identified and stated

Excellent in recognize problem, plan and develop alternative ideas

Require less or seldom supervision.

Understand and interpret information in lab sheet

The question to be answered during the lab is identified and stated in somewhat unclear manner

Good in recognize problem, plan and develop alternative ideas

Required occasionally supervision.

Moderately Understand and interpret information in lab sheet

The question to be answered during the lab is partially identified, but stated in somewhat unclear manner

Moderate in recognize problem, plan and develop alternative ideas

Required occasionally supervision.

Limited understand information in lab sheet

The question to be answered during the lab is erroneous or irrelevant

Satisfactory in recognize problem, plan and develop alternative ideas

Required frequent supervision.

Poor understand information in lab sheet

The question cannot be answered during the lab

Need improve in recognize problem, plan and develop alternative ideas

Required frequent supervision.

Following Procedure

Gladly helps other students to follow procedures.

Thoroughly and carefully follows each step before moving on to next step.

Will discuss with peers to solve problems in procedures.

Carefully follows each step.

Will ask peers for help with problems in lab procedures.

Works to follow each step before moving on to the next step.

Requires help from lecturer with some steps in procedures.

Often requires help from the lecturer to even complete basic procedures.

Practical Work Finding

Solutions are accurate and precise Observation are very thorough and

may recognize possible errors in data collection

Includes appropriate and significant answers.

Solutions are accurate with reasonable answer

Observation are very thorough Includes appropriate and significant

answers.

Solutions are mostly accurate Observation are generally complete Only 2 or 3 minor errors in the

answers.

Solutions are somewhat inaccurate and very precise

Observation are incomplete or recorded in confusing way

There are 3 or more minor errors in the answers.

Solutions are incomplete, inaccurate and imprecise

Observation are incomplete or not included

Answers are not included

CATEGORYCognitive

5 4 3 2 1

Analysis and Theory Application

Analyzes and interprets data: Able to use appropriate theory. translates theory into practice or

applies excellently to process model(s)

Analyzes and interprets data: applies appropriate theory to data

when prompted to do so. good interprets significance of

theory or variable involved.

Analyzes and interprets data: applies appropriate theory to data

when prompted to do so. misinterprets significance of

theory or variable involved.

Analyzes and interprets data: make attempt to related data to

theory. sometimes makes errors in unit

conversions

Analyzes and interprets data: make no attempt to related data

to theory. makes errors in unit conversions

Teamwork Skills Communication effectively. Well

focused and attention in doing experiment on time given.

Most communicate. Less focused and attention in doing experiment on time given.

Sometime communicate. Almost focused and attention in doing experiment but not on time given.

Lacked communication. Not focus and lost attention on several occasions.

None communication. Participation was minimal or student did not doing experiment.

Discussion

All important trends and data comparisons have been interpreted correctly and discussed, good understanding of results is conveyed.

Almost all of the results have been correctly interpreted and discussed, only minor improvements are needed.

Some of the results have been correctly interpreted and discussed; partial but incomplete understanding of results is still evident.

Very incomplete or incorrect interpretation of trends and comparison of data indicating a lack of understanding of results.

There is no discussion attached.

Conclusion All important conclusions have

been clearly made, student shows good understanding.

All important conclusions have been drawn, could be better stated.

Conclusions regarding major points are drawn, but many are misstated, indicating a lack of understanding.

Conclusions missing or missing the important points.

There is no conclusion attached.


THEORY :

A Relation is a mathematical concept based on the ideas of sets. The model was first proposed by Dr. E.F. Codd of IBM Research in 1970 in the following paper: “A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970”.Relational algebra is the basic set of operations for the relational model. These operations enable a user to specify basic retrieval requests (or queries). The result of an operation is a new relation, which may have been formed from one or more input relations.

Relational Algebra consists of several groups of operations

Unary Relational OperationsSELECT (symbol: (sigma))PROJECT (symbol: (pi))RENAME (symbol: (rho))

Relational Algebra Operations From Set TheoryUNION (), INTERSECTION ( ), DIFFERENCE (or MINUS, – )CARTESIAN PRODUCT ( x )

PROCEDURE :

Write the general form of all the statements below using Relational Algebra symbol. Then, based on appropriate table, generate the output.

1. SELECT SELECT is used to obtain a subset of the tuples of a relation that satisfy a select condition.

STAFF2

staffNo staffName Position Year

00111 Anaz Programmer 2

00500 Ali Web developer 4

00689 Samat Programmer 4

00444 Azie Web developer 3

00123 Hazlim Technical support 2

(a) Select only Position as a Web developer

ANSWER

General statementi. __________________________________________________________ii. __________________________________________________________


Output


00500 Ali Web developer 4

00444 Azie Web developer 3

b) Select only Position=Programmer and Year > 2

ANSWER


Output

STAFF2


00689 Samat Programmer 4


2. PROJECT The PROJECT operation is used to select a subset of the attributes of a relation by specifying the names of the required attributes.

CUSTOMERcustNo custName projectNo staffNoC001 Adam & co 1 00111C010 TegasTulinSdnBhd 2 00123C011 WangsaMewangiSdnBhd 2 00123C007 WawasanSdnBhd 5 00999C021 Halim& Son SdnBhd 3 00689

(a) Get a list of all customer number and customer name

ANSWER


Output

custNo custNameC001 Adam & coC010 TegasTulinSdnBhdC011 WangsaMewangiSdnBhdC007 WawasanSdnBhdC021 Halim& Son SdnBhd


3. JOIN

JOIN is used to combine related tuples from two relations: In its simplest form the JOIN operator is just the cross product of the two

relations. As the join becomes more complex, tuples are removed within the cross

product to make the result of the join more meaningful. JOIN allows you to evaluate a join condition between the attributes of the

relations on which the join is undertaken.

LECTURER SUPERVISORId_Lec Lec_Name Subject State Salary3120 Badariah 3101 Johor 2300

3333 Zanita 2113 Pahang 40005123 Khairul 0101 Selangor 32001273 Yogess 2211 Johor 18656545 Najwa 3418 Sarawak 5050

7777 TengKiat 3101 Sabah 2300

(a) Join table LECTURER and SUPERVISOR where Id_Lec equal to LecId.

ANSWER


OutputId_Lec = LecId Id_Student StudentName Lec_Name Subject State Salary

3120 1111 Ahmad Badariah 3101 Johor 23003333 2222 Salmah Zanita 2113 Pahang 40005123 3333 Zanita Khairul 0101 Selangor 32001273 4444 Ai Ling Yogess 2211 Johor 18656545 5555 Raju Najwa 3418 Sarawak 50507777 6666 Monaj TengKiat 3101 Sabah 23005123 7777 TengKiat Khairul 0101 Selangor 32001273 8888 Yamin Yogess 2211 Johor 1865

Id_Student StudentName LecId1111 Ahmad 31202222 Salmah 3333

3333 Zanita 51234444 Ai Ling 12735555 Raju 65456666 Monaj 7777

7777 TengKiat 51238888 Yamin 1273


4. CARTESIAN PRODUCT This operation is used to combine tuples from two relations in a combinatorial fashion

STAFF1

staffNo staffName Position

00689 Samat Electric engineer

00500 Ali Web developer

00111 Anaz Programmer

PROJECT

projectNo projectName Cost

1 DADD 1 000 000

2 Programming 500 000

3 Multimedia 8000

ANSWER


Output

projectNo projectName Cost staffNo staffName Position1 DADD 1 000 000 00689 Samat Electric

engineer1 DADD 1 000 000 00500 Ali Web developer1 DADD 1 000 000 00111 Anaz Programmer2 Programming 500 000 00689 Samat Electric

engineer2 Programming 500 000 00500 Ali Web developer2 Programming 500 000 00111 Anaz Programmer3 Multimedia 8000 00689 Samat Electric

engineer3 Multimedia 8000 00500 Ali Web developer3 Multimedia 8000 00111 Anaz Programmer


LABORATORY EXERCISE

Based on given table, answer all questions.

1) Select the STUDENT tuples whose Age number is 18 and State is Pahang:

STUDENTId_Student Name Age Sex State

1111 Ahmad 18 M Penang

2222 Salmah 19 F Johor

3333 Zanita 18 F Pahang

4444 Ai Ling 18 F Perak

5555 Raju 19 M Johor

6666 Monaj 20 M Penang

7777 TrngKiat 19 M Sabah

8888 Yamin 18 M Kedah

ANSWERGeneral Statement:i.ii.

Output

Id Student Name Age Sex State


2) Get a list of all project name and cost

PROJECTprojectNo projectName Cost

1 DADD 1 000 000

2 Programming 500 000

3 Multimedia 8000

ANSWER

General Statement:i.ii.


Output

PROJECTprojectName Cost

DADD 1 000 000Programming 500 000

Multimedia 8000

QUESTION

Answer the questions below by referring to the tables given.

STUDENTId_Student Name Age Sex State

1111 Ahmad 18 M Penang

2222 Salmah 19 F Johor


4444 Ai Ling 18 F Perak

5555 Raju 19 M Johor

6666 Monaj 20 M Penang

7777 TrngKiat 19 M Sabah

8888 Yamin 18 M Kedah

LECTURERId_Lec Lec_Name Subject State Salary Sex3120 Badariah 3101 Johor 2300 F3333 Zanita 2113 Pahang 4000 F5123 Khairul 0101 Selangor 3200 M1273 Yogess 2211 Johor 1865 F6545 Najwa 3418 Sarawak 5050 F7777 TengKiat 3101 Sabah 2300 M

SUBJECTSubject_Code Subject_Name Lecturer Credit_Hours

0101 C++ Khairul 32113 Fundamental of IT Zanita 23418 Database System Najwa 42211 Computer Hardware Yogess 23101 Network Security TengKiat 2


SUPERVISORId_Student StudentName LecId

1111 Ahmad 3120

2222 Salmah 3333

3333 Zanita 5123

4444 Ai Ling 1273

5555 Raju 6545

6666 Monaj 7777

7777 TengKiat 5123

8888 Yamin 1273

Instructions:

Draw the tables according to the relational algebra symbols below.

1. FEMALE_LECTURER ← σ sex='F' (LECTURER)

Id_Lec Lec_Name Subject State Salary Sex3120 Badariah 3101 Johor 2300 F3333 Zanita 2113 Pahang 4000 F1273 Yogess 2211 Johor 1865 F6545 Najwa 3418 Sarawak 5050 F

2. LECTNAMES ← π Lec_Name, Subject, Id_Lec (FEMALE_LECTURER)

Id_Lec Lec_Name Subject3120 Badariah 31013333 Zanita 21131273 Yogess 22116545 Najwa 3418


3. LECT_DEPENDENTS ← LECTNAMES X SUPERVISOR

Id_Lec Lec_Name Subject Id_Student StudentName LecId3120 Badariah 3101 1111 Ahmad 31203120 Badariah 3101 2222 Salmah 33333120 Badariah 3101 3333 Zanita 51233120 Badariah 3101 4444 Ai Ling 12733120 Badariah 3101 5555 Raju 65453120 Badariah 3101 6666 Monaj 77773120 Badariah 3101 7777 TengKiat 51233120 Badariah 3101 8888 Yamin 12733333 Zanita 2113 1111 Ahmad 31203333 Zanita 2113 2222 Salmah 33333333 Zanita 2113 3333 Zanita 51233333 Zanita 2113 4444 Ai Ling 12733333 Zanita 2113 5555 Raju 65453333 Zanita 2113 6666 Monaj 77773333 Zanita 2113 7777 TengKiat 51233333 Zanita 2113 8888 Yamin 12731273 Yogess 2211 1111 Ahmad 31201273 Yogess 2211 2222 Salmah 33331273 Yogess 2211 3333 Zanita 51231273 Yogess 2211 4444 Ai Ling 12731273 Yogess 2211 5555 Raju 65451273 Yogess 2211 6666 Monaj 77771273 Yogess 2211 7777 TengKiat 51231273 Yogess 2211 8888 Yamin 12736545 Najwa 3418 1111 Ahmad 31206545 Najwa 3418 2222 Salmah 33336545 Najwa 3418 3333 Zanita 51236545 Najwa 3418 4444 Ai Ling 12736545 Najwa 3418 5555 Raju 65456545 Najwa 3418 6666 Monaj 77776545 Najwa 3418 7777 TengKiat 51236545 Najwa 3418 8888 Yamin 1273


4. ACTUAL _DEP ← σ Id_Lec=LecId (LECT_DEPENDENTS)

Id_Lec = LecId Lec_Name Subject

Id_Student StudentName

3120 Badariah 3101 1111 Ahmad3333 Zanita 2113 2222 Salmah1273 Yogess 2211 4444 Ai Ling1273 Yogess 2211 8888 Yamin6545 Najwa 3418 5555 Raju

5. RESULT ← πLectName, Subject, StudentName (ACTUAL_DEP)

Lec_Name Subject StudentNameBadariah 3101 Ahmad

Zanita 2113 SalmahYogess 2211 Ai LingYogess 2211 YaminNajwa 3418 Raju


DISCUSSIONDiscuss and explain the result generated from the practical work 3 and what you have learned from this practical work.

CONCLUSION

Give conclusion from the discussion and practical work result.

Prepared by: Verified by:

……………………………..…. …………………………………….ShamsiahBintiSalamatCourse Coordinator Database System (EC601)Department of Electrical Engineering

Prepared by: Verified by:

……………………………..…. …………………………………….Isma Shamsuria Bt IsmailCourse Coordinator Database System (EC601)Department of Electrical Engineering

ec601-lab3

Documents