Database design and the er model database system concepts. Before your tutorial, work through and attempt all of the questions in the rst section. Exercises for murachs mysql my guitar shop database chapter 10. The rst step is to nd entity types that we wish or that the customer asks us to represent in the database. While i wrote some of the solutions myself, most were written originally by students in the database classes at wisconsin. A generic entity type that has a relationship with one or more subtypes. In the answer given here, the main entity sets are student, course, courseoffering, and instructor. Creating the database and other supporting structures based on a specific dbms conceptual design. Problem 2 35 points below we present several entity relationship diagrams for modeling information about authors, editors and books. Relationship eer model enhanced er eer model created to design more accurate database schemas reflect the data properties and constraints more precisely more complex requirements than traditional applications. Each person has a name, ssn, gender, address, and birth date. Be sure to include the office entity in this diagram.
Consider the database schema of the pharmaceutical company database exercise 3 mapping the eer model. Collection of related data represents some aspects of the real world data is logically coherent is provided iru dq intended group of users and dssolfdwlrqv relational database systems 1 twolf tilobalke tinstitut furinformationssysteme ttu braunschweig 2 exercise 1. Show the possibilities and discuss how to choose between the possibilities. The core of this product is a database with a schema that captures all the information that galleries need to maintain. The entity set course offering is a weak entity set dependent on course. In real applications, data often does not present as tables naturally. Because of special deals and volume discounts, the price at which a good is acquired is often different from its list price. We do not want to allow the database to hold data representing a man supervising a woman. There are many different variants of er diagramming.
Requirements the following are the requirements for the gym fitness database for each member we keep track of the unique memdid, a well as name, zip, and the date the membership was paid for each membership type we keep track of the unique mid, as well as mname and price. Eer modeling and mapping eer to relational model consider an online auction database system in which members buyers and sellers participate in the sale of items. Business requirements given create an er database model and map it into a relational schema gym fitness gym fitness database. The rules associated with the most commonly used normal forms, namely first. The current price of these goods is maintained though the historical price is not. Entityrelationship er diagrams northeastern university. Database i er diagram 2 database development we know how to query a database using sql a set of tables and their schemas are given data are properly loaded but, how can we develop appropriate tables and their schema for an application. Construct a clean and concise er diagram for the nhl database using the chen notation as in your textbook. Eer and mapping exercise solution exercise eer modeling.
Givearelationalschemadiagramforthefollowingerdiagram. Eer and mapping exercise solution exercise eer modeling and mapping eer to relational model consider an online auction database system in which. Relationship eer model enhanced er eer model created to design more accurate database schemas reflect the data properties and constraints more precisely more. Beginning database designers often doubt that anything could be a key by itself. For each member we keep track of the unique memdid, a well as name, zip, and the date. Enhanced entity relationship model tutorial to learn eer model in simple, easy and step by step way with examples and notes. Relational database design by er and eertorelational. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your er diagram. The administrators table is not related to the other six tables. Eer diagram exercises exercise 1 the requirements for the part of the university database. Database design 2nd edition covers database systems and database design concepts.
In this exercise you will also be given some of the design, asked to explain the meaning of the design in words and. Modern database management 12th edition solutions manual. The examples are there for additional preparation, practice, and revision. Eer model chapter 8 6e chapter 4 5e lecture outline extending the er model created to design more accurate database schemas reflect the data properties and constraints more precisely address more complex requirements subclasses, superclasses, and inheritance. Designs are pictures called entity relationship diagrams. Design an entityrelationship er schema diagram for the. Use mysql workbench to create an eer model from the script file.
We store each employees name first, last, mi, social security number ssn, street address, salary, sex gender, and birth date. Week 6 entityrelationship modelling and diagramming 1. Relational database design by er and eer torelational mapping chapter 9 database using an er or eer model, lets automatically convert it to relational form as a first draft implementation model. Introduction to databases er data modeling ae3b33osd lesson 8 page 2 silberschatz, korth, sudarshan s. For example, in the organisation modelled above, it will not be possible to record a second supervisor for an. In the answer given here, the main entity sets are student, course, course offering, and instructor. Choose one of the key attributes of e as the primary key for r. Convert each specialization with m subclasses s1, s2.
Eer model chapter 8 6e chapter 4 5e lecture outline extending the er model created to design more accurate database schemas. Remember to register for the course so that your exercise may be graded. Use mysql workbench to create an eer diagram for a database that stores. The online site has members who are identified by a unique member id and are described by an email.
An employee is assigned to one department, but may work on several projects, which are not necessarily controlled. In reality, we usually create unique ids for entity sets. Requirements the following are the requirements for the gym fitness database for each member we keep track of the unique memdid, a well as name, zip, and the date. Eer and mapping exercise solution exercise eer modeling and. Erd is allowed you to communicate with the logical structure of the database to users. The enhanced entityrelationship eer model or extended entityrelationship model in computer science is a highlevel or conceptual data model incorporating extensions to the original entityrelationship er model, used in the design of databases it was developed to reflect more precisely the properties and constraints that are found in more complex databases, such as in. Database design and the er model solutions to practice exercises 6. Define each of the following database terms relation primary key foreign key referential integrity field data type null value 9. Lecture outline using highlevel, conceptual data models for database design entityrelationship er model popular highlevel conceptual data model er diagrams diagrammatic notation associated with the er model. The data requirements for this system are summarized as follows.
Cs2312 example sheet 4 answers question 1 discuss the following terms from extendedentityrelationship eer modelling. Cs3200 database design spring 2018 derbinsky exercise draw an erd for the following description. Each department has a unique name, a unique number, and a particular employee who manages the department. Note that other diagrams are possible depending on assumptions. Extend a relational database with your own er design. Century, and you must design a database to keep track of the administration of the exhibition. Database schema design using entityrelationship approach. They make all entity sets weak, supported by all other entity sets to which they are linked. Make necessary changes to the eer diagram given to include the following. Consider three different alternatives for the problem in exercise 2. Thus, in this case, the information cannot be captured in both diagrams. Exercise 1 problem a company database needs to store information about employees identified by ssn, with salary and phone as attributes, departments identified by dno, with dname and budget as attributes, and children of employees with name and age as attributes.
Collection of database exam solutions rasmus pagh october 19, 2011 this is a supplement to the collection of database exams used in the course introduction to database. Relational database design by er and eerrto relational. When designing your database, an entityrelationship diagram er or erd is an excellent way to visually lay out your plan. Options for mapping specialization or generalization. But in some cases, you may want to opt for an enhanced entityrelationship diagram eer or eerd which includes a few more elements than er diagrams do. The following are the requirements for the gym fitness database. The database designer gains a better understanding of the information to be contained in the database with the help of erp diagram. A sample database application requirements gathered for company employees, departments, and projects company is organized into departments department controls several projects employee. Eer modelling task 1 introductory make ermodels for the following tasks.
In the answer given here, the main entity sets are. Show how each of the elements you describe is drawn in an eer model. See the writers database exercise in the exercise session for an example. Covers topics like features of eer model, sub class, super class, specialization, generalization, etc. Entityrelationship model solution the exercises marked with will be discussed in the exercise session. Thus, the database must retain the price at which a good is actually acquired. Pdf er diagram for university database kumaravel c. You can solve the other exercises as practice, ask questions about them in the session, and hand them in for feedback. The extended entityrelationship eer model is a conceptual.
The extended entityrelationship model the extended entityrelationship eer model entities, relationships and attributes cardinalities, identifiers and generalization documentation of eer diagrams and business rules modeling strategies 2004 john mylopoulos the extended entityrelationship model 2 conceptual modeling csc2507. Relational database systems 1 what is a database db. Multiplicities will allow us to optimise the database but the price to be paid is a restriction on what the database can store. Each person has a name, gender, address, and birth date and identified by ssn. The entity set courseoffering is a weak entity set dependent on course. The enhanced entityrelationship eer model or extended entityrelationship model in computer science is a highlevel or conceptual data model incorporating extensions to the original entityrelationship er model, used in the design of databases. For each regular strong entity type e in the er schema, create a relation r that includes all the simple attributes of e. Use mysql workbench to create an eer model from the script file named. Although you always wanted to be an artist, you ended up being an expert on databases because you love to cook data and you somehow confused. The style of diagram notation in these and later notes will differ somewhat from that used in the textbooks and in lectures. Exercises for murachs mysql my guitar shop database.
The database used in this exercise was designed using the extended entity relationship eer data model. For each of the task it is possible to model it in more than one way. An employee, regardless of sex, is assigned to exactly one office, with each office having exactly one employee in it. Chapter 4 normalization 2 data normalization formal process of decomposing relations with anomalies to produce smaller, wellstructured and stable relations primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. Create an er database model and map it into a relational schema gym fitness gym fitness database. A person in the system can belong to at least one or more of these types. We keep track of the start date when that employee began managing the department. The database should track how each representative voted on each bill. If the chosen key of e is composite, the set of simple attributes that form it will together form the primary key of r.
Relational database design by er and eerrto relational mapping. Relational model solution the exercises marked with will be discussed in the exercise session. From the model, create an eer diagram that shows the relationships between the seven. A person in the system belongs to at least one or more of these types. Examples include socialsecurity numbers, automobile vins etc. Your old love is still there, however, so you set up a database. Apr 23, 2015 this feature is not available right now. Database design 2nd edition the bc open textbook project. If you do not do the exercises then you are unlikely to pass the exam. In access, under the file menu, choose new, select database from new options fig 2.
The extended entityrelationship model the extended entity. Reflect the data properties and constraints more precisely. Using highlevel, conceptual data models for database design entityrelationship er model popular highlevel conceptual data model er diagrams diagrammatic notation associated with the er model 2. Assume that the mapping will create tables with simple singlevalued attributes. Database design and modeling modeling tutorials documenting the sakila database 9. The quantity needed for each ingredient is also kept. This database could be modeled as the single entity set exam, with attributes coursename, sectionnumber, roomnumber,andtime.
1464 1301 54 649 63 261 336 1073 1244 61 904 1042 1045 1252 964 78 1116 1262 1235 734 802 1252 888 774 9 1373 1282 840 1362 554 1245 378 738 652 840