Creation of logical relational database.

By -

Creation of logical relational database

EXAMPLES OF DATA BASE DESIGN

ORDER - VENDOR - ITEMS ORDERED EXAMPLE IN CASE STUDY

Information on dependencies given :

•Orders for item placed with many vendors

•A given order no is only to one vendor

•Many items supplied against a given order no

•A vendor has capacity to supply many items but only some items maybe ordered from him at a particular time

image

RELATIONS-UNNORMALIZED

EXAMPLES OF UNNORMALIZED RELATIONS

1. ORDERS(Order no,Order date)

2. ORDERS PLACED FOR(Order no,item code,qty ordered,delivery time allowed)

3. ORDERS PLACED WITH(order no,vendor code,item code)

4. VENDOR(Vendor code,vendor name,vendor address) 5.ITEM( item code,item name,price/unit)

6. SUPPLIES(vendor code,item code,order no,qty.supplied,date of supply)

NORMALIZATION:

Relation 1,4,5 are in 3NF and need no change

Relation 2 has a composite key,attributes of composite key not related. Non key attributes dependent on composite key,need no change.

Relation 3: order no and item code have multivalued dependency.Relation2

already has order no,item code as composite key.

Relation 3 is reduced to:

7. ORDER PLACED WITH(order no,vendor code)

NORMALIZATION OF SUPPLIES RELATION

Consider relation 6 :

6. SUPPLIES (vendor code, item code, order no, qty supplied, date of supply)

•It has a composite key with three attributes

•Attributes item code and order no have multi-valued dependency as many items can be supplied in one order

• And hence need normalization to 4NF

Normalized to

8. ACTUAL SUPPLIES (order no, item code, qty supplied, date of

supply)

9. VENDOR CAPABILITY (vendor code, item code )

The second relation may have items not yet ordered with a vendor but which could be supplied by vendor

The Normalized relations are : 1,2,4,5,7,8,9
STUDENT-TEACHER-COURSES EXAMPLE

Information on dependence

•A teacher may teach more than one course in a semester

•A teacher belongs to only one dept.

•A student may take many courses in a semester

•A course may have many sections taught by different teachers

E-R Diagram

image

RELATION-UNNORMALIZED

1 TEACHER (Teacher code,teacher name, address, rank, dept)

2 TEACHER_COURSES (Teacher code,Course no,no of students, section no )

3 COURSE (Course no , semester taught ,Course name, credits)

4 STUDENT (Student no, student name, dept, year )

5 STUDENT COURSES (Student no, Course no, semester no ) a)Relations 1,3,4 in 3NF

b) Relations 2 and 5 have multi-attribute key which has multi-valued dependency but do not need normalization

c)However information on which teacher teaches a given student a specified course cannot be found from relations 1 to 5

Therefore Add relation

6TEACHER_STUDENT (Teacher code, Student no, Course no)

THIS SET IS NORMALIZED

CONCLUSIONS

• We have seen how data relevant to applications are organized logically into set of relations

• The process of normalization depends on the semantics, i.e, meanings of data and an understanding of how various data elements are related

•It is thus a human intensive activity-it cannot be automated

• In most problems in practice one is satisfied with 3NF.Higher normal forms are theoretically important and in some cases becomes essential.

• There is a mathematical theory which underpins the idea of relations and normalization giving it a sound basis. We have not discussed it in this module.

• A full fledged course in Data Base will describe in detail the mathematical basis and methods of querying a database

Comments

Post a Comment

Popular posts from this blog

WORKED EXAMPLES ON PROCESS SPECIFICATION.

By -
Image
PROCESS SPECIFICATION WORKED EXAMPLES 6.1 A bank has the following policy on deposits: On deposits of Rs. 5000 and above and for three years or above the interest is 12%. On the same deposit for a period less than 3 years it is 10%. On deposits below Rs. 5000 the interest is 8% regardless of the period of deposit. Write the above process using (i) Structured English (ii) A decision table 6.2 An organization maintains an employee file in which each record has following data: { Employee No., employee name, employee gross pay}. It has been decided to increase the pay as per the following formula: For pay of Rs. 1000 or less increase 15%. For pay of more than Rs. 1000 but up to Rs. 2500 increase 10%. For pay over Rs. 2500 increase 5%. (i) Writ e a structured English processing rule corresponding to the above policies. (ii) Express the policies as a decision table. (i) While employee records left in file do Read Number, name , gross pay 6.3 An offshore gas company bills it
Read more

Why do we need information systems, management structure, requirements of information at different levels of management.

By -
Image
Why do we need information systems, management structure, requirements of information at different levels of management NEED FOR INFORMATION SYSTEMS Information systems are needed when timely processing for fast action is needed, same data has to be processed in different ways and when organizations require innovative processing. MANAGEMENT STRUCTURE Functional areas of management are as follows: •PRODUCTION • MARKETING • MATERIALS – purchase, stores • FINANCE –Accounts • HUMAN RESOURCE DEVELOPMENT(HRD) • RESEARCH AND DEVELOPMENT (R&D) INFORMATION FOR MANAGEMENT A Production Management The following type of information is needed in production management: Strategic Information: 1)Yearly and monthly production quotas and alternate schedules 2)Policies on machine replacement, augmentation and modernization. 3)Identifying best product mix. Tactical Information 1)Identifying and controlling areas of high cost. 2) Identifying critical bottlenecks in production. 3)
Read more

The User Interface:Establishing User Interfaces

By -
Image
The User Interface Establishing User Interfaces The success factors in analysis start with the established interfaces from day one. What does this mean? You must start the process by meeting with the right people in the organization. In the best projects, the process is as follows: 1. Executive interface: There needs to be an executive-level supporter of the project. Without such a supporter, you risk not being able to keep the project on schedule. Most important, you need a supporter for the political issues that you may need to handle during the project (discussed in detail later). The executive supporter, sometimes known as a sponsor (JAD reference), should provide a preliminary schedule advising the organization of what is expected and the objectives of the project. The executive supporter should attach a letter to the preliminary schedule and send it to the project team members. The letter must put the importance of the project into perspective. Therefore, it is strongly rec
Read more