数据库e-r图（Database E-R diagram）

数据库e-r图（Database E-R diagram） 数据库e-r图(Database E-R diagram) 0. Describe the process of database conceptual design using E-R method. Answer: E-R method was used for database conceptual design, can be divided into 3 steps: firstly, the design of local E-R mode, and then put all local E-R model into a global E-R model, at the end of the global E-R model, obtain the final E-R model, namely conceptual model. 1, a university credit system, students can choose according to their own circumstances. Each student may take multiple courses at the same time. Each course can be taught by a number of teachers. Each teacher may teach multiple courses. The incomplete E-R diagram is shown in figure 1. Point out the connection type between students and courses. Point out the connection type between courses and teachers. If each student has a teacher who guides each teacher to more than one student, how does the student relate to the teacher? In the original E-R map, make up the drawing teacher and student contact, and perfect E-R diagram. Answer: The types of students' contact with courses are many to many. The type of connection between curriculum and teachers is many to many. The types of connections between students and teachers are one to many. Complete the results of the subject E-R diagram as shown in figure 2. 2, the E-R diagram shown in Figure 3 is converted to relational mode, and the attributes in the diamond box are determined by themselves. Answer: the E-R diagram of the subject is translated into the following relational schema: Unit (unit number, address, telephone) Staff (number of workers, name, sex, age, unit number) 3 、 assume that a department's database includes the following information: Staff information: employee number, name, address and department. Department information: Department of all staff, department name, manager and sales of products. Product information: product name, manufacturer, price, model and product internal number. Manufacturer's information: Manufacturer's name, address, product name and price. Try drawing the E-R diagram of this database. A: the E-R diagram corresponding to the subject is shown in figure 4. 4, a hospital ward computer management center needs the following information: Department: Department of family name, address, telephone, doctor name Ward: ward number, bed number, the name of the Department Doctor: name, title, section name, age and work card number Patient: medical record number, name, gender, diagnosis, doctor in charge, ward number Among them, the number of wards, how many doctors of a department, a unit can only belong to a department, a doctor only belongs to a department, but may be responsible for the diagnosis and treatment of multiple patient, the doctor in charge of a patient with only one. Complete the following design: The E-R diagram of the computer management system is designed Converts the E-R diagram into a relational schema structure. Indicates the post selection code for each relational schema in the conversion 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 of family, address, telephone) Ward (ward number, bed number, department name) Doctor (work ID number, name, title, department name, age) Patient (medical record number, name, gender, doctor in charge, ward number) (3) the post selection code for each relation is as follows: The code is selected after the family. The post selection code in the ward is the name of department + ward number. The doctor's post code is a work permit. The patient's post code is the medical record number. 5, with the following entities: Student: student number, unit name, name, gender, age and elective course Courses: number, class name, course unit, class number, teacher number Teacher: teacher number, name, gender, title, lecture number Unit name, telephone number, teacher number and teacher name The following links exist in the above entities: One student may have multiple courses, and one course may be taken by multiple students. A teacher can teach many courses, and one course can be taught by more than one teacher. A unit can have more than one teacher, one teacher can only belong to one unit. Try the following tasks: Two local E-R diagrams are designed respectively for student course selection and teacher training. The E-R graph completed by the above design is combined into a global E-R graph. A logical structure of a database that converts a global E-R graph into an equivalent relational schema representation. Answer: (1) the student chooses a class local E-R diagram, as shown in Figure 6, teacher's classroom local E-R diagram, as shown in figure 7. (2) the merged global E-R diagram is shown in figure 8. To avoid the complexity, the merged global E-R diagram omits the properties of the following entities: Unit name, telephone number Student: student number, name, gender, age Teacher: teacher number, name, gender, title Course: number, course name (3) the global E-R graph is transformed into an equivalent relational schema, and the data logic structure represented is as follows: Unit (unit name, telephone number) Courses (teacher number, name, gender, title, unit name) Students (student number, name, sex, age, unit name) Lecture (teacher number, course number) Elective (student number, course number) 6, is shown in Figure 9 (a), (b) and (c) 3 different partial models, and merge them into a global information structure, and set the properties of the associated entity (permission to increase the necessary attributes, can also be the basic entity attribute selection for the associated entities). The entities are as follows: Department: department number, department name, telephone number, address Clerk: clerk number, name of staff, position (cadre / worker), age and sex Equipment: unit number, telephone number, address Worker: worker number, name, specification and price Equipment: equipment number, name, specification and price Parts: part number, name, specification and price Manufacturer: unit number, name, telephone number, address Answer: the summarized E-R diagram is shown in figure 6.20. The attributes of various entities are: Department: department number, department name, telephone number, address Worker: worker number, worker name, duty, age, sex Equipment: equipment number, name, specification and price Parts: part number, name, specification and price 7, a library lending management database requires the following services: (1) the variety, quantity and storage position of the existing books in the stack room can be checked at any time. All kinds of books can be uniquely identified by the isbn. (2) you can always inquire about the books, including the library unit, the name, the library card number, the date of borrowing books and the date of return. We agree that anyone can borrow many kinds of books, any kind of books can be borrowed by many people, and the library card number is unique. (3) when required, the relevant publishing houses may purchase additional books through the press numbers, telephone numbers, postal codes, and addresses stored in the database. We agree that a publishing house can publish many kinds of books. The same book is only published by a publishing house, and the name of a publishing house is unique. According to the above situation and hypothesis, try to design as follows: Constructing a E-R diagram satisfying demand. Converts relational schema structures into equivalence. A: (1) the E-R diagram that meets the above requirements is shown in figure 11. (2) convert the equivalent relational schema to the following: Borrower (library card number, name, unit) Books (ISBN, title, number, location, press name) Publisher (publisher name, telegraph number, telephone number, zip code, address) Borrow (card number, number, date books, book the date) 8, the factory (including factory name and factory name) need to establish a management database to store the following information: A factory has many workshops, each workshop has workshop number, workshop director name, address and telephone number. A workshop has a number of workers, each worker has worker number, name, age, sex and type of work. A workshop produces a variety of products, Products have product numbers and prices. (4) a workshop produces a variety of parts, and a part may be made for multiple workshops. Parts have part number, weight and price. (5) a product consists of a variety of parts, and a component can also be assembled with a wide range of products. (6) products and parts are stored in the warehouse. (7) there are many warehouses in the factory, the warehouse has the warehouse number, the name and the telephone number of the warehouse director. Try: (1) draw the E-R diagram of the system. (2) the corresponding relation model is given. (3) draw a hierarchical schema map of the system. Answer: (1) the E-R diagram of the system is shown in figure 12. The attributes of each entity are: Factory: name of factory, name of factory director Workshop: workshop number, workshop director name, address, telephone number Worker: worker number, name, age, sex, type of work Warehouse: warehouse number, warehouse director name, telephone number Parts: part number, weight, price Product: product number and price (2) the corresponding relationship patterns are as follows: Name of factory (factory name, factory name) Workshop (workshop number, workshop director name, address, telephone number, factory name) Worker (worker number, name, age, sex, type of work, workshop number) Warehouse (warehouse number, warehouse director name, telephone number, long name) Products (product number, price, shop number, warehouse number) Part (part number, weight, price, warehouse number) Manufacturing (shop number, part number) (3) the hierarchical model diagram of the system is shown in figure 13. 8, there are two aspects of the following sports teams and sports clubs: 1. sports teams Sports teams: name, name, name of the team coach The team name, team name, gender, name Among them, a sports team has more than one player, one player only belongs to a sports team, and a team usually has a coach. 2., the sports meeting Sports teams: team number, name, name of the coach Item: name of project, team number, name of team member, gender and venue Among them, a project can be attended by several teams, an athlete may participate in a number of projects, a project, a venue. Please complete the following design: Two local E-R diagrams are designed respectively for the sports team and the sports meeting. Merge them into a global E-R diagram. What conflicts do you have when merging? How do you resolve these conflicts? A: (1) the local E-R map of the sports team is shown in Figure 14. The local E-R map of the sports meeting is shown in figure 15. (2) the result of the merger is shown in figure 6.26. (3): name, project name naming conflicts synonym, named as the project name. Structure conflict: the project is composed of two local E-R diagrams, one attribute, one entity, and the merging entity. 9, suppose to establish an enterprise database, the enterprise has more than one unit, each unit has a plurality of employees, an employee belongs to only one unit, and an employee works only in a project, but a project has many employees to work, there are a number of available to be different for each project to supply equipment. The attributes of a unit are the unit name and the telephone number. Staff attributes are: staff number, name, gender. The attributes of the device include: device number, device name, origin. The supplier's attributes include name and telephone number. The attributes of the project include: engineering name and location. Please complete the following process: Design a E-R diagram that meets the above requirements. Convert the E-R diagram into an equivalent relational schema. According to your understanding, underline the code in each relationship. Answer: (1) the E-R diagram that satisfies the requirement is shown in figure 17. The properties of each entity are as follows: Unit (unit name, telephone) Staff (staff number, name, sex) Equipment (equipment name, equipment number, origin) Supplier (name, telephone) Engineering (engineering name, site) (2) the transformed relational schema is as follows: Unit (unit name, telephone) Staff (worker number, unit name, project name, name and sex) Equipment (equipment name, equipment number, origin) Supplier (name, telephone) Engineering (engineering name, site) Supply (supplier name, project name, equipment number, quantity) (3), see (2) underline. Figure 10, reflects a corporate sector (DEPT) and workers (EMP), engineering (PROJ), materials (PART) and material suppliers (SUPP) and warehouse (WH) between E-R. Establish its relationship model. Answer: the corresponding relationship model is as follows: Department (department number, department name,...) Staff and workers (worker number, worker name, department number, engineering number,...) Works (works number, project name,...) Material (material number, material name,...) Material suppliers (supply firms, name,...) Warehouse (warehouse number, warehouse name, location,...) INV (warehouse number, material number) S-PR-PA (supply firm, nominal number, material number) The underlined property or property group is code. 11, set up a naval base to establish a fleet management information system, which includes the following two aspects of information: 1. fleet aspects Fleet name, base site, number of ships Ship: number, name of vessel, name of fleet 2. naval aspects Ship: Ship number, vessel name, weapon name Weapons: weapon name, weapon production time, ship number Officers and men: number of officers and men, name, vessel number Among them, a fleet of warships, a ship belonging to a fleet; a ship mounted a variety of Wuqi, weapons can be installed on various ships; a ship with a plurality of soldiers, a brigade only belongs to a ship. Please complete the following design: Two local E-R diagrams of fleet and ship are designed respectively. Combine the two local E-R graphs into a global E-R graph. Converts the global E-R diagram into relational schema. Is there a naming conflict in the merge? How to deal with it? A: (1) the two local E-R diagrams of the fleet and the ship are shown in fig.. (2) combine the two local E-R graphs shown in Fig. 19 and FIG. 20 into the global E-R graph as shown in figure 21. (3) the relation model of transformation is as follows: Fleet (fleet name, base location) Ship (ship number, ship name, fleet name, number of ships) Officers and men (number of officers and men, name, vessel number) Weapon (weapon name, weapon production time) Install (ship number, name Qi) (4) conflict exists: "Ship number" and "number" synonym, with unified "ship no.". "Ship" and "ship name" synonym, with unified "ship name". 12, a commercial agency group database has 3 sets of entities, is a "commodity" entity sets, attribute store number, store name and address; the two is the "commodity" entity sets, attribute of commodity, commodity name, specification, unit price; three is the "workers" entity set, have a worker number, name, gender, performance etc.. There are "selling" contact the store with the goods, each store can sell a variety of goods, each commodity can also be placed in a number of shops selling a commodity, each store has monthly sales of filial piety; "employment" link between the store and the staff, each store has a lot of employees, each employee can work in a shop the store hired workers, duration of employment and wages. Try drawing the E-R diagram. The E-R graph is transformed into relational schema, and the main and outer codes are pointed out. Answer: (1) the corresponding E-R diagram is shown in figure. (2) the E-R can be converted to the following relationship model: Store (store number, shop name, address) shop number for main yards Employees (employee number, name, gender, performance, store, employment, wages) the number of employees mainly store code as the outer code. Commodity (commodity number, commodity name, specification, unit price) commodity number as the main code Sales (store number, commodity number, monthly sales volume), shop number + commodity number, main yards, shop numbers, commodity numbers are all outside codes There are several lines, 13 schools, each department has several classes and department, there are several teachers each department, some professors and associate professors each with several graduate students, there are a number of students in each class, each student elective several courses, each course elective by some students. Please draw the conceptual model of the school in E-R, and the attributes of the entity can be designed by yourself. Answer: the corresponding E-R diagram is shown in figure 23. The properties of each entity are as follows: Department: Department name, department head number, department address, telephone number Class: class number, number of monitor. Department: Department of these names, address, telephone Student: student number, name, gender, age, native place, year of entry, major Undergraduate students: completed credits, GPA Graduate student: research direction, tutor name Teacher: name, age, sex, title, Expertise Associate Professor, research project, research direction