Construct an entity relationship diagram

Assignment Help Database Management System

Reference no: EM13777838 , Length: 5

Section A

Question One

a) Define the following terms:

i. A candidate key

ii. A superkey

b) Explain the differences between the notions of Database (DB), Database Management System (DBMS) and Database System (DBS).

c) List at least four responsibilities of a database management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged, If these responsibilities were not met by a given DBM, what problems can Occur?

d) Explain the difference between a weak and a strong entity set.

e) We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?

f) What is the meaning of the term 'functional dependency'? Why is it important to establish functional dependencies during normalization?

Section B

Question Two

a) A Maseno university registrar's office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.

i. Construct an E-R diagram for the registrar's office.

ii. Document all assumptions that you make about the mapping constraints.

b) In your own word explain how data integrity is maintained within the database when concurrent users access the database

Question Three

Consider the insurance database, where the primary keys are underlined. Construct the following SQL queries for this relational database.

person (driver-id. name, address)

car (license, model, year)

accident (report-number, date location)

owns (driver-id, license)

participated (driver-id. car. report-number, damage-amount)

a) Find the total number of people who owned cars that were involved in accidents in 2014.

b) Find the number of accidents in which the cars belonging to "Ivy Isaac" were involved.

c) Add a new accident to the database; assume any values for required attributes.

d) Delete the Mazda belonging to "Ivy Isaac".

e) Update the damage amount for the car with license number "KJE 539" in the accident with report number "AR2197" to KSh. 300,000.

Question Four

a) Consider the following table instance chart

Table name: EMPLOYEE

Column Name	EMP ID	EMP NAME	ADDRESS	JOIN DATE
Key Type	PK
Null/Unique	NN, U	NN		NN
Default Value				System Date
Check	1 to 999999
Data Type	Number	VARCHAR2	VARCHAR2	Date
Length	10	30	50

b) Create the EMPLOYEE table based on the table instance chart shown above. Choose the appropriate data types and be sure to add integrity constraints

C) You are asked to design a database system for a health club. The database would contain data about customers, their training, contact numbers, etc. Show the main steps you would perform for designing and implementing the database.

d) In your own word explain how data integrity is maintained within the database when concurrent users access the database

Question Five

Use the following table to answer the questions that follow:

WORK.RECORD

Emp	EmpName	Project	Project	Dept.	Dept.	Grade	Grade	Start
No		No.	Name	No.	Loc.	No	Title	Date
4321	Omcndi	PA2.32	Alpha	:A32	KS'''	SR:	Grinder	241/07
						SR2	Grinder	241/09
⁷;67	Lucy	PA2.32	Alpha	LASS	NKR	SR:	Grinder	241/07
						GR3	Grinder	241/09
6745	Omondi	PAS.90	Alpha	:A32	KSM	FN:	Packer	3/8106
4519	Mtua	PAS.¹9	Omega	:A19	KSM	. I (NI I n I., (,) (4	Packer	30.34r
							Grinder	212/09
							Grinder	21/2/010

i. Give TWO reasons why the WORKRECORD table is not in third norma form.

ii. Fully identify one occurrence of redundant data.

iii. Write down the functional dependencies implied by the table.

iv. Normalise the table as far as third normal form showing the intermediate stages and the primary keys. Write down any assumptions you make.

v. Construct an entity relationship diagram to show the relationships between the structures you have identified. Write down any assumptions you make.

Reference no: EM13777838

Questions Cloud

Uses operation costing-quantities and direct materials costs : Z Co manufactures two sizes of T-shirts, medium and large. Both sizes go through cutting, assembling and finishing departments. The company uses operation costing. Z Co's conversion costs applied to products for the month of June were: Cutting Depart..

Labor-management problems : Are current labor laws capable of dealing with labor-management problems, or should they be abolished? If abolished, what should their replacements (if any) address

The sodium potassium atpase pump : How many subunits does the sodium potassium ATPase pump have ? Ma.1b.2c.3d.4

Value chain through vertical integration : In some cases one of the purposes of initiating a business is to control your value chain through vertical integration. General Motors relies on an unrelated company for a key component. General Motors relies on a vertically integrated subsidiary for..

Construct an entity relationship diagram : Explain the differences between the notions of Database (DB), Database Management System (DBMS) and Database System (DBS).

Number of kanban cards increase issue : How does input from other areas into PAC and the feedback PAC provides impact PAC? Explain.

Production cost report using the weighted-average method : Edenton Boat Company manufactures small pleasure boats on an assembly-line basis. The units are started in the Department A. On July 1 of this year, the Work-in-Process inventory of the department A consisted of 200 units 100% complete as to material..

Programming with sql using number and date fun : Programming with SQL Objectives: Using Character, Number and Date Functions. • Create and execute single-row functions that perform case conversion and/or character manipulation. • Create and execute single-row number functions ROUND and TRUNC. •..

Should a department policy be updated with each new case : As a student of human resource management in criminal justice organizations, you have studied the potential effects and cost associations of section 1983 Liability lawsuits arising out of the violation of certain protected Civil Rights. Should a d..

User Account

All Pages