Database Design Concept

BTEC Higher National Diploma in Software Development Assignment Specification Institute name

: IDM Computer Studies Pvt. Ltd.

Unit Name

: Database Design Concept

Unit Value

: Unit 4

Date given

: 23rd September 2008

Date to be submitted

: 23rd October 2008

Lecturer

: Ms. Gayasri Yahampath

Rational The objective of this assignment is to assure that the students have gained the relevant knowledge according to the outcomes specified in the syllabus. Assessment requirements to meet learning outcomes 1. 2. 3. 4.

Understanding database environments Use and manipulate appropriate database software Design a database Demonstrate the database

Scheme of Assessment: PASS Once you complete the tasks given will be eligible only for PASS. MERIT In addition to completing the tasks, if the student has • Drawn diagram clearly and completely with the correct cardinality • Correct SQL cording for the given scenario • Reasonable comparison of approaches & explanations. • Correct set of tables and fields after 3NF with related data

IDM/HND/Database Design concept

Page 1 of 4

DISTINCTION In addition to completing the tasks, if the student has • • • • •

Drawn diagram clearly and completely with the all the correct cardinality Produced high quality documentation Correct SQL cording for the given scenario Well organized comparison of approaches & explanations with examples. Correct set of all tables and fields after 3NF with related data

Task 01 a. Produce ER diagram which represents the following domain of insurance. A policy holder may have a number of policies with the insurance company. Each policy is given a policy number and relates to a single policy-holder. The company has a range of insurance product and may put tighter a range of products to form a policy. Examples of motor products are third party, fire, theft, accident damage, windscreen cover, etc. Brokers sell policies for commission and any one policy may have commission payable to more than one booker. Claims are made against policies. A claim relates to only one policy and each claim is classified according to one of six claim types. The companies products are grouped by business area, i.e. life, motor, marine, etc. any particular product belongs to only one business area. The company holds information on clubs and associations, for promotions. In order to limit risk the company may place all or part of a policy with re-insures. All or part of a single policy may be placed with a number of different re-insurers. b. Show the participation cardinalities of all relationships. Outcomes and Assessment Criteria • Demonstrate the database design to a third party • Provide clear justifications as to the structure of the database or the use of particular tools and techniques • Provide supporting documentation to complement the design Task 02 a. Compare the advantages and disadvantages of the traditional file systems and database management system. b. Briefly explain the five database management principles. Explain the role of the database administrator. c. Briefly explain the three data modules. Outcomes and Assessment Criteria • Provide evidence to support a knowledge and understanding of database environments • Examine a range of issues those are integral to database environments such as the end user, use across different platforms and compatibility • Identify the importance of DBMS in commercial and non-commercial environments IDM/HND/Database Design concept

Page 2 of 4

Task 03 a. Explain the Normalization Steps. b. Normalize the following un-normalized table to 3rd Normal Form (1NF, 2NF and 3rd NF). Modules moduleName

staffNo

Relational Database System

234

staffNam e Davies T

studentNo student 34698

Smith S

37798 34888

Jones Patel P

assGrad e B3

Relational Database Design

234

Davies T

34698

Smith S

B1 B2 B1 B3 B2

Deductive Database

345

Evans R

34668

Smith J

B3 A1

assType cwk1 cwk2 cwk1 cwk1 cwk2 cwk1 cwk2 exam

Outcomes and Assessment Criteria • • •

Apply normalisation techniques to a given data set Use a range of database tools and techniques Design a fully working database

IDM/HND/Database Design concept

Page 3 of 4

Task 04 Operating Schedule Doctor Doctor No Name 78654 Smith 78654 Smith 18654 Smith 18592 Jones

OpNo

OpDate

OpTime

AA1234 BA1598 AA1235 FG1965

04/02/1999 04/02/1999 13/02/1999 04/02/1999

08:30 10:30 14:00 16:00

Patient No 2468 3542 2468 1287

Patient Name Davies M. Jones D. Davies M. Evans I.

Addmission Date 20/01/1999 11/01/1999 20/01/1999 25/12/1999

c. Write the SQL code that will produce a table, containing the same information that was shown in the above table. d. i. Write the SQL Code to INSERT one record into the above table. ii. Write the SQL Code to display the records. iii. Write the SQL Code to UPDATE ‘Evans’ operation date to 06/01/2000 iv. Write the SQL Code to display the details of doctors who conduct the operation on 04/02/1999 v. Write the SQL Code to count the number of records of the above table. Outcomes and Assessment Criteria • • •

Use database or database function applications software Develop a range of sample input and output screens eg tables, forms and reports, dropdown lists, checkboxes, etc Critique the software used and state how it will be used to develop your own database design End of Assignment

IDM/HND/Database Design concept

Page 4 of 4