Conceptual structure design of database design---------e-R diagram detailed (various examples) __ database

http://blog.csdn.net/zxq1138634642/article/details/9121363

0, the process of using E-R method to design the database concept is discussed.

A : the use of E-R method for database conceptual design, can be divided into 3 steps: First, the design of local E-R mode, and then the local E-R model integrated into a global E-R mode, and finally the global E-R mode optimization, get the final E-R mode, that is,

1, a university realizes the credit system, the student may choose according to own circumstance the course. Each student may take a number of courses at the same time, each subject can be taught by a number of teachers, and each teacher can teach a number of courses. Its incomplete e-R diagram is shown in Figure 1.

(1) Indicate the type of contact between students and courses.

(2) Indicate the type of contact between the course and the teacher.

(3) If each student has a teacher guidance, each teacher guides many students, the student and the teacher is how to contact.

(4) in the original E-R map to complement the teacher and students contact, and improve the E-R diagram.

Answer:

(1) Students and curriculum contact type is a many-to-many relationship.

(2) The type of contact between the course and the teacher is a many-to-many relationship.

(3) The type of contact between students and teachers is a one-to-many relationship.

(4) Perfect The result of the E-R diagram as shown in Figure 2.

2. Convert the E-R diagram shown in Figure 3 to the relational mode, and determine the properties of the Diamond box.

A: The E-R diagram of the subject is converted to the following relational mode:

Unit (unit number, address, telephone)

Employee (Employee number, name, sex, age, Unit number)

3. Assume that a departmental database includes the following information:

(1) Employee's information: Employee number, name, address and department.

(2) Departmental information: All employees, department names, managers and products sold by the department.

(3) Product information: Product name, manufacturer, price, model and the internal number of products.

(4) Manufacturer's information: manufacturer's name, address, product name and price of production.

Try to draw an E-R diagram of this database.

A : the corresponding e-R figure is shown in Figure 4.

4. A hospital ward Computer Management Center needs the following information:

Department: Branch name, branch address, branch telephone, doctor name

Ward: Ward number, bed number, department name

Doctor: Name, title, department name, age, work permit number

Patient: Medical record number, name, sex, diagnosis, doctor in charge, Ward number

Among them, a section of how many wards, how many doctors, a ward can only belong to a department, a doctor belongs to only one department, but can be responsible for the diagnosis and treatment of a number of patients, a patient in charge of a doctor only one.

Complete the following design:

(1) Designing the E-R diagram of the Computer Management system

(2) converting the E-R diagram into a relational schema structure.

(3) The following code is indicated for each relational pattern in the transformation result.

Answer:(1) The E-R diagram of the subject is shown in Figure 5.

(2) The corresponding relational schema structure is as follows:

Department (branch name, branch address, branch telephone)

Ward (ward number, bed number, department name)

Doctor (ID number, name, title, department name, age)

Patient (medical record number, name, sex, doctor in charge, Ward number)

(3) The following selection code for each relationship is as follows:

The Department's choice of the code is the section name.

Ward after the choice of code is the department name + ward number.

The doctor's rear selection is a work permit.

The patient's rear selection is the medical record number.

5. The following entities are provided:

Student: School number, unit name, name, sex, age, elective course name

Course: Number, course name, starting unit, teaching number

Teacher: Teacher number, name, gender, title, teaching course number

Units: unit name, telephone number, teacher's name, teacher's names

The following links exist in the above entities:

(1) A student can choose a number of courses, a course can be selected by a number of students.

(2) A teacher can teach many courses and one course can be taught by a number of teachers.

(3) A unit can have multiple teachers, a teacher can only belong to one unit.

Try to complete the following tasks:

(1) Design students to select the class and teacher classroom two local e-R diagram.

(2) The E-R diagram completed by the above design is merged into a global e-R diagram.

(3) Transforming the global e-R diagram into the logical structure of the database represented by the equivalent relational pattern.

A :(1) The student chooses the course local e-R diagram as shown in Figure 6, the teacher teaches the local e-R diagram as shown in Figure 7.

(2) The merged global E-R figure is shown in Figure 8.

To avoid complexity, the merged global e-R diagrams omit the attributes of the following entities:

Unit: unit name, telephone

Student: School number, name, gender, age

Teacher: Teacher number, name, gender, title

Course: number, course name

(3) The global e-R diagram is converted to the equivalent relational pattern represented by the data logic structure as follows:

Unit (unit name, telephone)

Course (teacher number, name, gender, title, organization name)

Students (student number, name, sex, age, organization name)

Lectures (teacher number, course number)

Elective (school number, course number)

6, Figure 9 gives (a), (b) and (c) 3 different local models, merging them into a single global information structure and setting properties in the contact entity (allowing for additional attributes that are considered necessary, or selecting properties for the underlying entity as the associated entity).

The entities are constituted as follows:

Department: Department number, department name, telephone number, address

Clerk: Staff number, employee name, position (cadre/worker), age, sex

Equipment Department: Unit number, telephone, address

Worker: Worker number, name, specification, price

Equipment: Equipment number, name, specification, price

Parts: Part number, name, specification, price

Manufacturer: Unit number, name, telephone number, address

A : after the summary of the E-R figure shown in figure 6.20.

The attributes for each entity are:

Department: Department number, department name, telephone number, address

Employee: Employee number, employee name, position, age, sex

Equipment: Equipment number, name, specification, price

Parts: Part number, name, specification, price

7, a library lending management database requirements for the following services:

(1) The variety, quantity and location of existing books in the stacks can be inquired at any time. All types of books can be uniquely identified by the ISBN.

(2) At any time to check the books to borrow, including the borrower's unit, name, library card number, library date and return date.

We agree: Anyone can borrow a variety of books, any kind of books can be borrowed by many people, the library card number has uniqueness.

(3) When necessary, can be stored in the database by the Telegraph number of publishers, telephone, postal code and address and other information under the corresponding publishers to increase the purchase of books. We agreed that a publishing house can publish a variety of books, the same book only for a publishing house, the name of the publishing house is unique.

Based on the above conditions and assumptions, try the following design:

(1) Construct the E-R diagram satisfying the demand.

(2) conversion to the equivalent relational schema structure.

A :(1) The E-R diagram satisfying the above requirements is shown in Figure 11.

(2) conversion to the equivalent relational schema structure is as follows:

Borrowers (library card number, name, unit)

Books (ISBN, title, quantity, position, publishing house name)

Publishing house (press name, Telegraph number, telephone, zip code, address)

Borrowing (library card number, ISBN, library date, book return date)

8, the factory (including plant name and factory name) need to establish a management database to store the following information:

(1) A factory has more than one workshop, each workshop has workshop number, workshop director name, address and telephone.

(2) A workshop has a number of workers, each worker has employee number, name, age, gender and type of work.

(3) A workshop produces a variety of products, products have product numbers and prices.

(4) A workshop produces a variety of parts, a part may also be made for a number of workshops. Parts have part number, weight and price.

(5) A product consists of a variety of parts, a part can also assemble a variety of products.

(6) products and parts are stored in the warehouse.

(7) There are many warehouses in the factory, warehouse has warehouse number, warehouse director name and telephone.

Try: (1) to draw the E-R diagram of the system.

(2) The corresponding relational model is given.

(3) Draw out the hierarchy pattern diagram of the system.

A :(1) The E-R diagram of the system is shown in Figure 12.

The properties for each entity are:

Factory: Name of factory and director

Workshop: Workshop number, workshop director name, address, telephone

Workers: Employee number, name, age, sex, type of work

Warehouse: Warehouse number, warehouse director name, telephone

Parts: Part number, weight, price

Products: Product number, price

(2) The corresponding relational model is as follows:

Factory (factory name, director name)

Workshop (workshop number, workshop director name, address, telephone number, factory name)

Workers (employee number, name, age, sex, type of work, workshop number)

Warehouse (warehouse number, warehouse director name, telephone number, long name)

Products (product number, price, workshop number, warehouse number)

Parts (part number, weight, price, warehouse number)

Manufacturing (workshop number, part number)

(3) The hierarchical model diagram of the system is shown in Figure 13.

8, there are the following sports teams and games two entities:

1. Sports team Aspect

Sports Teams: Team name, coach name, player name

Players: Team name, team name, sex, item name

Among them, a sports team has a number of players, a team members belong to only one team, a team usually has a coach.

2. Sports meeting Aspect

Sports Teams: Team number, team name, coach name

Project: Name of the project, participation in the sports team number, name of the team, sex, competition venue

Among them, a project can be participated by a number of teams, one athlete can participate in multiple projects, a project a competition venue.

Please complete the following design:

(1) Design sports teams and two local e-R diagrams respectively.

(2) Merge them into a global e-R diagram.

(3) What conflicts exist when merging, and how do you resolve these conflicts.

A :(1) The local E-R figure of the sports team is shown in Figure 14, the local E-R figure in the games is shown in Figure 15.

(2) The results of the merge are shown in Figure 6.26.

(3) Naming conflict: Item name, project name is synonymous with name, unified named Project name.

Structure conflict: The project in two local E-R diagram, one as a property, one as an entity, merging unified as the entity.

9, suppose to establish an enterprise database, the enterprise has a number of subordinate units, each unit has more than one worker, a worker only subordinate to a unit, and a worker only in a project work, but a project has a lot of workers to participate in work, there are many suppliers for various projects supply different equipment. The unit's properties are: unit name, telephone. Employee's attributes are: Employee number, name, sex. The properties of the device are: equipment number, equipment name, origin. The vendor's attributes are: name, telephone. The properties of the project are: project name, location.

Please complete the following processing:

(1) Design to meet the above requirements of E-R diagram.

(2) Convert the E-R diagram into the equivalent relational mode.

(3) According to your understanding, underline the code in each relationship.

　　　A :(1) The E-R diagram satisfying the requirements is shown in Figure 17.

The properties of each entity are as follows:

Unit (unit name, telephone)

Employee (Employee number, name, sex)

Equipment (equipment name, equipment number, origin)

Supplier (name, telephone)

Project (project name, location)

(2) After the conversion of the relationship model is as follows:

Unit (unit name, telephone)

Employee (Employee number, unit name, project name, name, sex)

Equipment (equipment name, equipment number, origin)

Supplier (name, telephone)

Project (project name, location)

Supply (supplier name, project name, equipment number, quantity) (3) See Underline (2).

10. The diagram reflects an E-R diagram of the links between a company Department (Dept), Employees (EMP), Engineering (proj), materials (part), Material supplier (SUPP) and warehouse (WH). Establish its relational model.

A : the corresponding relational model is as follows:

Department (department number, department name, ...) ）

Employee (Employee number, employee name, department number, project number, ...). ）

Engineering (project number, project name, ...) ）

Materials (material number, material name, ...) ）

Material Supplier (supplier number, name, ...) ）

Warehouse (warehouse number, warehouse name, location, ...) ）

Inv (warehouse number, material number)

S-PR-PA (supplier number, public weigh, material number)

The attribute or attribute group with an underscore is a code.

11, set up a naval base to establish a fleet management information system, which includes the following two aspects of information:

1. Fleet side

Fleet: Name of Fleet, location of base, number of ships

Ship: Number, warship name, fleet name

2. Naval aspects

Warship: Warship number, warship name, weapon name

Weapon: Weapon name, weapon production time, warship number

Officers and Soldiers: ID number, name and warship number

Among them, a fleet of more than one ship, a ship belonging to a fleet; a warship installs a variety of Wu Qi, a weapon can be installed on many ships, a ship has several officers and soldiers, a soldier and soldiers belong to only one ship.

Please complete the following design:

(1) The design of two local e-R diagrams of fleet and warship respectively.

(2) Merging the two local E-r graphs into a global e-R diagram.

(3) The global e-R diagram is converted into a relational mode.

(4) Whether there is a naming conflict when merging. How to deal with.

A :(1) the fleet and the warship two local e-R diagrams are shown in the figure.

(2) The two local e-R diagrams shown in Figure 19 and figure 20 are merged into the global e-R diagram shown in Figure 21.

(3) The transformation of the relational model is as follows:

Fleet (fleet name, base location)

Vessel (ship number, name of warship, fleet name, number of ships)

Officers and soldiers (ID, name, warship number)

Weapon (weapon name, weapon production time)

Installation (ship number, Wu Qi name)

(4) There is conflict, manifested in:

The "Ship number" and "number" have synonyms, which are unified as "ship numbers" when merged.

"Warship name" and "warship name" exist synonyms, the unification of "warship name".

12. There are 3 entity sets in the database of a business group, one is "commodity" entity set, the property has store number, store name, address and so on; the second is "commodity" entity set, the attribute has commodity number, commodity name, specification, unit price, etc. three is "employee" entity set, attribute has employee number, name, sex, achievement etc.

There is a "sales" link between the store and the merchandise, each store can sell a variety of goods, each product can be placed in a number of stores to sell, each store filial piety of a commodity has a monthly sales; there is a "hire" link between the store and the employee, and every shop has many employees, each worker can only work in one store, The shop employs employees with duration and wages.

(1) Try to draw the E-R diagram.

(2) The E-R diagram is converted into a relational mode, and the main code and the outer code are pointed out.

A :(1) The corresponding e-R figure as shown in the figure.

(2) This E-R diagram can be converted to the following relational mode:

Store (store number, store name, address) store number main code

Employee (Employee number, name, sex, performance, store number, duration, salary) employee number is the main code, store number is outside code.

Goods (commodity number, commodity name, specification, Unit price) commodity number is the main code

Sales (store number, commodity number, monthly sales) store number + commodity number for the main code store number, the product number is outside the code

13, there are several departments in the school, each department has a number of classes and teaching and research, each teaching section has a number of teachers, some professors and associate professor each with a number of postgraduate students, each class has a number of pupils, each student elective several courses, each course can be selected by a number of students. Please use e-R to draw out the concept of this school model, entity properties can be designed.

A : the corresponding e-R figure is shown in Figure 23. The properties of each entity are as follows:

Department: Department name, department head number, department address, Department telephone

Class: Class number, monitor, number

Teaching and researching Office: name, address, telephone

Student: School number, name, gender, age, origin, year of entry, professional

Undergraduate: Completed credits, average score

Postgraduate: Research direction, mentor name

Instructor: Name, age, gender, title, specialty

Associate Professor: Scientific research project, research direction

Transferred from Baidu Library: