Modern Database Management Solution Book

(1)

SOLUTIONS TO REVIEW QUESTIONS

(2)

2

Solutions to Review Questions and Exercises

Part One Background 4

Chapter 1 Introduction to Databases... 4

Chapter 2 Database Environment ... 6

Chapter 3 The Relational Model... 8

Chapter 4 Database Planning, Design, and Administration ... 12

Part Two Methodology 14 Chapter 5 Entity-Relationship Modeling... 14

Chapter 6 Normalization... 17

Chapter 7 Methodology - Conceptual Database Design ... 22

Chapter 8 Methodology - Logical Database Design for Relational Model... 23

Chapter 9 Methodology - Physical Database Design for Relational DBMSs ... 25

Chapter 10 Conceptual Database Design Methodology - Worked Example ... 27

Chapter 11 Logical Database Design Methodology - Worked Example ... 29

Chapter 12 Physical Database Design Methodology – Worked Example ... 32

Part Three Database Languages 33 Chapter 13 SQL ... 33

Chapter 14 Advanced SQL... 39

Chapter 15 QBE... 46

Part Four Selected Database Issues 47 Chapter 16 Security... 47

Chapter 17 Transaction Management... 49

Chapter 18 Query Processing ... 54

Part Five Current Trends 62 Chapter 19 Distributed DBMSs - Concepts and Design ... 62

Chapter 20 Distributed DBMSs - Advanced Concepts ... 67

Chapter 21 Introduction to Object DBMSs ... 73

(3)

Chapter 23 Object-Relational DBMSs ... 84

Part Six Future Trends 89

Chapter 24 Web Technology and DBMSs ... 89 Chapter 25 Data Warehousing... 91 Chapter 26 OLAP and Data Mining... 93

(4)

4

Part One Background Chapter 1 Introduction to Databases

Review Questions

1.1 List four examples of database systems other than those listed in Section 1.1.

Some examples could be:

• A system that maintains component part details for a car manufacturer;

• An advertising company keeping details of all clients and adverts placed with them;

• A training company keeping course information and participants' details;

• An organization maintaining all sales order information.

1.2 Discuss each of the following terms:

Data For end users, this constitutes all the different values connected with the various objects/entities that are of concern to them. (See also Section 1.3.3)

Database (See Section 1.3.1)

Database Management System (See Section 1.3.2)

Data Independence This is essentially the separation of underlying file structures from the

programs that operate on them, also called program-data independence. (See also Sections 1.2.2 and 1.3.1)

Security The protection of the database from unauthorized users, which may involve

passwords and access restrictions. (See also Section 1.6)

Integrity The maintenance of the validity and consistency of the database by use of

particular constraints that are applied to the data. (See also Section 1.6)

Views These present only a subset of the database that is of particular interest to a

user. Views can be customized, for example, field names may change, and they also provide a level of security preventing users from seeing certain data. (See also Section 1.3.2)

1.3 Describe the approach taken to the handling of data in the early file-based systems. Discuss the

disadvantages of this approach.

Focus was on applications for which programs would be written, and all the data required would be stored in a file or files owned by the programs. (See also Section 1.2).

Clearly, each program was responsible for only its own data, which could be repeated in other program’s data files. Different programs could be written in different languages, and would not be able to access another program’s files. This would be true even for those programs written in the same language, because a program needs to know the file structure before it can access it. (See also Section 1.2.2).

1.4 Describe the main characteristics of the database approach and contrast it with the file-based

approach.

Focus is now on the data first, and then the applications. The structure of the data is now kept separate from the programs that operate on the data. This is held in the system catalog or data dictionary. Programs can now share data, which is no longer fragmented. There is also a reduction in redundancy, and achievement of program-data independence. (See also Section 1.3)

1.5 Describe the five components of the DBMS environment and discuss how they relate to each

other.

See Section 1.3.3.

(5)

Data Administrator See Section 1.4.1

Database Administrator See Section 1.4.1

Logical Database Designer See Section 1.4.2

Physical Database Designer See Section 1.4.2

Application Programmer See Section 1.4.3

End Users See Section 1.4.4

1.7 Discuss the advantages and disadvantages of database processing.

See Section 1.6 Exercises

1.8 Interview some users of database systems. Which DBMS facilities do they find most useful and

why? Which DBMS facilities do they find least useful and why? What do these users perceive to be the advantages and disadvantages of the DBMS?

Select a variety of users for a particular DBMS. If the users are using different DBMSs, group the answers for the different systems, which will give an overall picture of specific systems.

1.9 Write a small program that allows entry and display of renter details including a renter number,

name, address, telephone number, preferred number of rooms and maximum rent. The details should be stored in a file. Enter a few records and display the details. Now repeat this process but rather than writing a special program, use any DBMS that you have access to. What can you conclude from these two approaches?

The program can be written in any appropriate programming language, such as Pascal, FORTRAN, C. It should adhere to basic software engineering principles including being well-structured, modular, and suitably commented. It is important to appreciate the process involved even in developing a small program such as this. The DBMS facilities to structure, store, and retrieve data are used to the same effect. The differences in the approaches, such as the effort involved, potential for extension, ability to share the data should be noted.

1.10 Study the DreamHome case study presented in Section 1.7. In what ways would a DBMS help this

organization? What data can you identify that needs to be represented in the database? What relationships exist between the data? What queries do you think are required?

It may be useful to review the file-based approach and the database approach here before tackling the first part of the exercise. Careful reading and thinking about how people might use the applications should help in carrying out the rest of the exercise.

1.11 Study the Wellmeadows Hospital case study presented in Appendix A. In what ways would a

DBMS help this organization? What data can you identify that needs to be represented in the database? What relationships exist between the data?

(6)

6

Chapter 2 Database Environment

2.1 Discuss the concept of data independence and explain its importance in a database environment.

See Section 2.1.5

2.2 To address the issue of data independence, the ANSI-SPARC 3-level architecture was proposed.

Compare and contrast the 3 levels of this model.

See Section 2.1

2.3 What is a data model? Discuss the main types of data models.

An integrated collection of concepts for describing data, relationships between data and constraints on the data in an organization. (See also Section 2.3).

Object-based data models such as the entity-relationship model (see Section 2.3.1). Record-based data models such as the relational data model, network data model, and hierarchical data model (see Section 2.3.2).

2.4 Discuss the function and importance of conceptual modeling.

See Section 2.3.4.

2.5 Describe the types of facilities you would expect to be provided in a multi-user Database

Management System.

Data Storage, Retrieval and Update Authorization Services

A User-Accessible Catalog Support for Data Communication

Transaction Support Integrity Services

Concurrency Control Services Services to Promote Data Independence

Recovery Services Utility Services

See also Section 2.4

2.6 Of the facilities in 2.4, which ones do you think would not be needed in a standalone PC

Database Management System? Provide justification for your answer.

Concurrency Control Services - only single user.

Authorization Services - only single user, but may be needed if different individuals are to use the DBMS at different times.

Utility Services - limited in scope.

Support for Data Communication - only standalone system.

2.7 Describe the main components in a DBMS and suggest which components are responsible for

each facility identified in question 2.5.

Query Processor, DML Preprocessor, Data Storage, Retrieval and

Query Optimizer, Data Manager Update

Dictionary Manager A User-Accessible Catalog

Data Manager Transaction Support

Scheduler Concurrency Control Services

Utilities Recovery Services

Authorization Control Authorization Services

Utilities Support for Data Communication

Integrity Checker Integrity Services

Database Manager, DDL Compiler, File Manager

(7)

Utility Services See also Sections 2.5 and 2.4.

2.8 What is meant by the term "client-server architecture" and what are the advantages of this

approach? Compare the client-server architecture with two other architectures.

The client is a process that requires some resource, and the server provides the resource. Neither need reside on the same machine. Advantages include:

• Better performance

• Likely reduction in hardware costs

• Reduction in communication costs

• Better consistency See also Section 2.6.

2.9 Discuss the function and importance of the data dictionary.

See Section 2.7 Exercises

2.10 Analyze the DBMSs that you are currently using. Determine each system’s compliance with the

functions that we would expect to be provided by a DBMS. What types of languages does each system provide? What type of architecture does each DBMS use? Check the accessibility and extensibility of the data dictionary. Is it possible to export the data dictionary to another system?

To do this you will need to obtain appropriate information about each system. There should be manuals available or possibly someone in charge of each system who could supply information.

2.11 Write a program that stores names and telephone numbers in a database. Write another program

that stores names and addresses in a database. Modify the programs to use external, conceptual, and internal schemas. What are the advantages and disadvantages of this modification?

The programs can be written in any suitable language and should be well structured and appropriately commented. Two distinct files result. The structures can be combined into one containing name, address, and tel_no, which can be the representation of both the internal and conceptual schemas. The conceptual schema should be created separately with a routine to map the conceptual to the internal schema. The two external schemas also must be created separately with routines to map the data between the external and the conceptual schema. The two programs should then use the appropriate external schema and routines.

2.12 Write a program that stores names and dates of birth in a database. Extend the program so that it

stores the format of the data in the database; in other words, create a data dictionary. Provide an interface that makes this data dictionary accessible to external users.

Again, the program can be written in any suitable language. It should then be modified to add the data format to the original file. This should not be difficult, if the original program is well structured. The interface for other users operates on the data dictionary and is separate from the original program. A menu-based interface is adequate.

2.13 How would you modify this program to conform to a client-server architecture? What would be

the advantages and disadvantages of this modification?

The server should hold the data dictionary and the programs that operate on it. The user interface should be separate, on the client, and call the data dictionary programs.

(8)

8

Chapter 3 The Relational Model

3.1 Discuss each of the following concepts in the context of the relational data model:

(a) Relation

(b) Attribute

(c) Tuple

(d) Intension and Extension

(e) Degree and Cardinality.

Each term defined in Section 3.2.1.

3.2 Discuss the differences between the candidate keys and the primary key of a relation. Explain

what is meant by a foreign key. How do foreign keys of relations relate to candidate keys?

The primary key is the candidate key that is selected to identify tuples uniquely within a relation. A foreign key is an attribute or set of attributes within one relation that matches the candidate key of some (possibly the same) relation.

3.3 Define the two principal integrity rules for the relational model. Discuss why it is desirable to

enforce these rules.

Two rules are Entity Integrity (Section 3.3.2) and Referential Integrity (Section 3.3.3).

3.4 Define the five basic relational algebra operations. Define the remaining three relational algebra

operations in terms of the five basic operations.

Five basic operations are:

• Selection and Projection (Unary)

• Cartesian Product, Union and Set Difference (Binary). There is also the Join, Intersection and Division operations:

• Can rewrite θ-Join in terms of the basic selection and Cartesian product operations:

R F S = σF(R × S)

• Can express the intersection operator in terms of the set difference operation: R ∩ S = R - (R - S)

• Can express the division operator in terms of the basic operations: T1 = ΠC(R)

T2 = ΠC( (S x T1) - R) T = T1 - T2

3.5 What is a view? Discuss the difference between a view and a base relation. Explain what happens

when a user accesses a database through a view.

View is the dynamic result of one or more relational operations operating on the base relations to produce another relation. Base relation exists as a set of data in the database. A view does not contain any data, rather a view is defined as a query on one or more base relations and a query on the view is translated into a query on the associated base relations.

(9)

Exercises

The following tables form part of a database held in a relational DBMS:-Hotel (Hotel_No, Name, Address)

Room (Room_No, Hotel_No, Type, Price)

Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No) Guest (Guest_No, Name, Address)

where Hotel contains hotel details and Hotel_No is the primary key

Room contains room details for each hotel and (Hotel_No, Room_No) forms the primary key Booking contains details of the bookings and the primary key comprises (Hotel_No, Guest_No,

and Date_From)

and Guest contains guest details and Guest_No is the primary key.

3.6 Generate the relational algebra for the following queries:

(a) List all hotels.

HOTEL

(b) List all single rooms with a price below £20 per night.

σtype='S' AND price < 20(ROOM)

(c) List the names and addresses of all guests.

Πname, address(GUEST)

(d) List the price and type of all rooms at the Grosvenor Hotel.

Πprice, type(ROOM hotel_no (σname='Grosvenor Hotel'(HOTEL)))

(e) List all guests currently staying at the Grosvenor Hotel.

GUEST guest_no (σdate_from <= '01-01-99' AND date_to >= '01-01-99' ( BOOKING hotel_no (σname='Grosvenor Hotel'(HOTEL)))) (substitute '01-01-99' for today’s date).

(f) List the details of all rooms at the Grosvenor Hotel, including the name of the guest

staying in the room, if the room is occupied.

(ROOM hotel_no (σname='Grosvenor Hotel'(HOTEL)) // Outer Join

Πguest.name, hotel.hotel_no, room.room_no(

(GUEST guest_no (σdate_from <= '01-01-99' AND date_to >= '01-01-99' ( BOOKING hotel_no (σname='Grosvenor Hotel'(HOTEL)))) (substitute '01-01-99' for today’s date).

(g) List the guest details (Guest_No, Name and Address) of all guests staying at the

Grosvenor Hotel.

Πguest_no, name, address(GUEST guest_no (σdate_from <= '01-01-99' AND date_to >= '01-01-99' ( BOOKING hotel_no (σname='Grosvenor Hotel'(HOTEL)))))

(10)

10

3.7 Using relational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price

details. What would be the advantages of this view?

Πroom_no, hotel_no, type(ROOM hotel_no (σname='Grosvenor Hotel'(HOTEL))) Security - hides the price details from people who should not see it.

Reduced complexity - a query against this view is simpler than a query against the two underlying base relations.

3.8 Produce the equivalent tuple and domain relational calculus statements for the above queries.

Tuple Relational Calculus

(a) RANGE OF H IS HOTEL {H}

(b) RANGE OF R IS ROOM

{R | R.Type = 'S' AND R.Price < 20} (c) RANGE OF G IS GUEST

{G.Name, G.Address} (d) RANGE OF H IS HOTEL

RANGE OF R IS ROOM

{R.Price, R.Type | ∃H (R.Hotel_No = H.Hotel_No AND H.Name = 'Grosvenor Hotel')}

(e) RANGE OF H IS HOTEL RANGE OF G IS GUEST RANGE OF B IS BOOKING

{G | B ((B.Date_From <= '01-01-99' AND

B.Date_To >= '01-01-99') AND (B.Guest_No = G.Guest_No) AND

∃H (B.Hotel_No = H.Hotel_No AND H.Name = 'Grosvenor Hotel'))}

(f) Need to use Union of a relation containing all Rooms that are occupied with a relation extended by a NULL name for all unoccupied rooms.

(g) RANGE OF H IS HOTEL RANGE OF G IS GUEST RANGE OF B IS BOOKING

{G.Guest_No, G.Name, G.Address | B((B.Date_From <= '01-01-99' AND B.Date_To >= '01-01-99') AND

(B.Guest_No = G.Guest_No) AND

∃H (B.Hotel_No = H.Hotel_No AND H.Name = 'Grosvenor Hotel'))} Domain Relational Calculus

(a) {Hotel_No, Name, Address | ∃Hotel_No, Name, Address (HOTEL(Hotel_No, Name, Address)}

(b) {Room_No, Hotel_No, Type, Price | ∃Room_No, Hotel_No, Type, Price (ROOM(Room_No, Hotel_No, Type, Price) AND

Type = 'S' AND Price < 20)}

(c) {Name, Address | ∃Name, Address (GUEST(Name, Address)} (d) {Price, Type | ∃Room_No, Type, Price, Hotel_No

(11)

(ROOM(Hotel_No, Type, Price) AND

∃Name (HOTEL(Hotel_No, Name) AND Name = 'Grosvenor Hotel')}

(e) {Guest_No, Guest.Name, Guest.Address | ∃Guest_No, Name, Address, (GUEST(Guest_No, Name, Address) AND

∃Date_From, Date_To, Hotel_No

(BOOKING(Hotel_No, Guest_No, Date_From, Date_To) AND Date_From <= '01-01-99' AND Date_To >= '01-01-99') AND

∃Name (HOTEL(Hotel_No, Name) AND Name = 'Grosvenor Hotel')))}

(f) Rest similar to above.

3.9 Explain how the entity and referential integrity rules apply to these relations.

For each relation, the primary key must not contain any nulls.

Room is related to Hotel through the attribute Hotel_No. Therefore, the Hotel_No in Room should either be null or contain the number of an existing hotel in the Hotel relation. In this case study, it would probably be unacceptable to have a Hotel_No in Room with a null value.

Booking is related to Hotel through the attribute Hotel_No. Therefore, the Hotel_No in Booking should either be null or contain the number of an existing hotel in the Hotel relation. However, because Hotel_No is also part of the primary key, a null value for this attribute would be unacceptable. Similarly for Guest_No. Booking is also related to Room through the attribute Room_No.

3.10 Analyze the RDBMSs that you are currently using. Determine the support the system provides for

primary keys, alternate keys, foreign keys, relational integrity and views. What types of relational languages does the system provide? For each of the languages provided, what are the equivalent operations for the eight relational algebra operations?

(12)

12

Chapter 4 Database Planning, Design, and Administration

4.1 Discuss the relationship between the information system lifecycle and the database application

lifecycle.

See Sections 4.1 and 4.2.

4.2 Describe the purpose of each of the stage of the database application lifecycle.

See Section 4.2.

4.3 Identify some of the techniques available to help document the users' requirements specification.

See Section 4.2.3.

4.4 Describe the main aims of the conceptual and logical database design phases.

The aims of conceptual database design are described in Section 4.3.2 and the aims of logical database design are described in Section 4.3.3.

4.5 Explain why it is necessary to select the target database management system before commencing

with the physical database design phase. Describe the main aims of physical database design.

Physical database design is tailored to a specific DBMS, therefore it is essential the DBMS is determined before the physical design phase can begin. The aims of physical database design are described in Section 4.3.4

4.6 Describe the prototype approach and identify the potential advantages of using this approach.

See Section 4.2.7.

4.7 Outline the procedure for selecting a DBMS.

See Section 4.6.

4.8 Define the purpose and tasks associated with data administration and database administration.

See Section 4.7. Exercises

(13)

4.10 Assume that you are responsible for selecting a new DBMS product for a group of users in your

organization. To undertake this exercise, you must first establish a set of requirements for the group and then identify a set of features that a DBMS product must provide to fulfill the requirements. Describe the process of evaluating and selecting the best DBMS product.

The student should follow the approach to DBMS selection described in Section 4.6 and produce a report that identifies a suitable DBMS product that meets the requirements of the organization. The selection should be fully justified.

4.11 Assume that you are responsible for selecting a DBMS product for the Wellmeadows Hospital

case study. Describe the process of evaluating and selecting the best DBMS product.

The student should follow the approach to DBMS selection described in Section 4.6 and produce a report that identifies a suitable DBMS product that meets the requirements of the

Wellmeadows Hospital case study. The selection should be fully justified and any assumptions

made about the case study should be highlighted..

4.12 Investigate whether data administration and database administration exists as distinct

functional areas within your organization. If identified, describe the organization, responsibilities, and tasks associated with each functional area.

The student should investigate the organization and identify whether data administration and database administration exists as distinct functional areas. However, the student should be careful to note that these functions may be named differently, merged as a single function or included as part of a larger IT/IS function. If identified, the student should compile a report documenting the organization, responsibilities, and tasks.

Doctor Clinic

NOK

Supply OPClinic Appointmt InPatient

Patient

Item Drug Medication

Staff Ward Bed

M 1 1 1 1 1 1 1 Supplier Requisitio M M M M M M M M M M M M M 1 M M M M M N M M 1 1 1 1 1 1 1 1 M Hospital Supplies Ward Management Patient Management d M N Attend Refers RelatedT Takes Form I Requires Has WorksIn WorksIn Is GoesT Examine Has Makes For AssignedTo Receives Provides 1

(14)

14

Part Two Methodology Chapter 5 Entity-Relationship Modeling

5.1 Describe the purpose of high-level data models in database design.

The main purpose for developing a high-level data model is to support a user’s perception of data and to conceal the more technical aspects associated with database design. Furthermore, a conceptual data model is independent of the particular DBMS and hardware platform that is used to implement the database.

5.2 Describe the basic concepts of the Entity-Relationship (ER) model. Present the diagrammatic

representation of these concepts.

The basic concepts of the Entity-Relationship model include entity types, relationship types, and attributes.

See Section 5.1

5.3 Describe the constraints that may be placed on participating entities in a relationship.

There are two main types of restrictions on relationships called cardinality and participation constraints.

See Section 5.2

5.4 Describe the problems that may occur when creating an ER model.

Problems may occur due to the misinterpretation of the meaning of certain relationships and these problems are referred to as connection traps. There are two main types of connection traps called fan traps and chasm traps.

See Section 5.3

5.6 Describe the main concepts associated with the Enhanced Entity-Relationship model. Present the

diagrammatic representation of these concepts.

The main concepts of the EER model are specialization/generalization, aggregation and categorization. However, in the accompanying textbook, we focus only on specialization/ generalization and categorization, which is associated with the related concepts of entity types described as superclasses or subclasses and the process of attribute inheritance.

See Section 5.4 Exercises

The University Accommodation Office Case Study

The Director of the University Accommodation Office requires you to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process based on the Director’s view has provided the following requirements specification for the Accommodation Office database.

(1) The data stored on each full-time student includes the matriculation number, name (first and last name), home address (street, city/town, postcode), date of birth, sex, category of student (for example, first year undergraduate (1UG), postgraduate (PG)), nationality, smoker (yes or no), special needs, any additional comments, current status (placed/waiting), and what course the student is studying on. The student information stored relates to those currently renting a room

(15)

and those on the waiting list. Students may rent a room in a university owned hall of residence or student flat. When a student joins the University he or she is assigned to a member of staff who acts as his or her Advisor of Studies. The Advisor of Studies is responsible for monitoring the student’s welfare and academic progress. The data held on a student’s Advisor includes their full name, position, name of department, internal telephone number, and room number.

(2) Each hall of residence has a name, address, telephone number, and a hall manager who supervises the operation of the hall. The halls provide only single rooms, which have a room number, place number, and monthly rent rate. The place number uniquely identifies each room in all the halls controlled by the Accommodation Office and is used when renting a room to a student.

(3) The Accommodation Office also offers student flats. These flats are fully furnished and provide single room accommodation for groups of 3, 4, or 5 students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat. Each bedroom in a flat has a monthly rent rate, a room number, and a place number. The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.

(4) A student may rent a room in a hall or student flat for various periods of time. New lease agreements are negotiated at the start of each academic year with a minimum rental period of one semester (15 weeks) and a maximum rental period of one year, which includes Semesters 1, 2, and the Summer Semester. Each individual lease agreement between a student and the Accommodation Office is uniquely identified using a lease number. The data stored on each lease includes the lease number, duration of the lease (given as semesters), name, and matriculation number of the student, place number, room number, address details of the hall or student flat, the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

(5) Student flats are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.

(6) Some information is also held on members of staff on the Accommodation Office and includes the staff number, name (first and last name), home address (street, city/town, postcode), date of birth, sex, position (for example, Hall Manager, Administrative Assistant, Cleaner), and location (for example, Accommodation Office or Hall).

(7) The Accommodation Office also stores a limited amount of information on the courses run by the University including the course number, course title (including year), course leader’s name, internal telephone number, and room number, and department name. Each student is associated with a single course.

(8) Whenever possible, information on a student’s next-of-kin is stored which includes the name, relationship, address (street, city/town, postcode), and contact telephone number.

5.6 Create an Enhanced Entity–Relationship (EER) model to represent the data requirements of the

University Accommodation Office case study. Develop the model using the following the steps:

(a) Identify entity types.

(b) Identify relationship types and determine the cardinality and participation constraints of the relationships.

(c) Identify attributes and associate attributes with entity or relationship types. (d) Determine candidate and primary key attributes.

(e) Specialize / generalize entity types (where appropriate). (f) Categorize entity types (where appropriate).

(g) Draw the EER diagram.

State any assumptions you made when creating the EER model.

An example of a possible ER model for the University Accommodation Office case study is shown below.

(16)

16

The student should identify any ambiguities in the specification for the University Accommodation Office and state clearly his or her assumptions. For example, we may assume that we only hold information on a single next-of-kin per student and that some students do not provide this information.

Lease Matric Course Reques Studi Fo Related Run Advis AcdStaff Place Provid HallN FlatN Manag Works O IDat Located StaffN Lease Studen Cours Academi Staff NOK Room Accomm adatio Hall Flat Accom Staff Undertak Office d M M 1 1 M 1 M Inspecti 1 1 1 1 M 1 M 1 1 M M M 1 1 1 1 M ∩ ∩

(17)

Chapter 6 Normalization

6.1 Describe the purpose of normalizing data.

When we design a database for a relational system, the main objective in developing a logical data model is to create an accurate representation of the data, its relationships and constraints. To achieve this objective, we must identify a suitable set of relations. A technique that we can use to help identify such relations is called normalization. Normalization is a technique for producing a set of relations with desirable properties, given the data requirements of an enterprise. Normalization supports database designers by presenting a series of tests, which can be applied to individual relations so that a relational schema can be normalized to a specific form to prevent the possible occurrence of update anomalies.