Entity Relationship Models - Courses

2y ago
32 Views
4 Downloads
394.09 KB
31 Pages
Last View : 17d ago
Last Download : 2m ago
Upload by : Allyson Cromer
Transcription

Entity‐RelationshipEntityRelationship ModelsZaki MalikSeptember 23, 2008

Exercise #1A university database contains information about professors(id tifi d by(identifiedb sociali l securityit number)b ) andd courses(identified by courseid). Professors teach courses; each ofthe following situations concerns the Teaches relationshipset. For each situation, draw an ER diagram that describesit. Professors can teach the same course in several semesters,semestersand each offering must be recorded.2

Exercise #2Professors can teach the same course in several semesters,and only the most recent such offering needs to berecorded.3

Exercise # 3 and 4 Every professor teaches exactly one course (no more, noless) Every professor teaches exactly one course (no more, noless), and every course must be taught by some professor4

Practice Professors have an SSN, a name, an age, a rank, and a research specialty.Projects have a project number, a sponsor name (e.g.,(NSF),) a starting date, anending date, and a budget.5

Graduate students have an SSN, a name, an age, and a degree programEach project is managed by exactly one professor (known as PI)Each project is worked in by one or more professors (known as Co‐PIs)Each project is worked on by one or more graduate students (known as RAs)6

When graduate students work on a project, a professor must supervise their workon the project. Graduate students can work on multiple projects, in which casethey will have a potentially different supervisor for each project.Departments have a department number, a department name, and a main office.Department has a professor (known as Chairman) who runs the department.7

Professors work in one or more departments, and for each department that theywork in, a time percentage is associated with their jobGraduate students have one major department in which they are working on theirdegree.Each graduate student must have another, more senior graduate student as anadvisor.advisor8

9

Exercise # 5 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 ofemployees (with name and age as attributes). Employees work inddepartments;eachh departmentdis managedd byb an employee;la childh ld mustbe identified uniquely by name when the parent (who is an employee;assume that only one parent works for the company) is known. Draw an ER diagram that captures this information.

A company database needs to store information about employees(identified by ssn, with salary and phone as attributes), departments(id tifi d by(identifiedb dno,dwithith dnamedandd budgetb d t as attributes),tt ib t ) andd childrenhildoffemployees (with name and age as attributes).

Employees work in departments;each department is managed by an employee;

a child must be identified uniquely by name when the parent (who is anemployee; assume that only one parent works for the company) is known.

Exercise # 6 You set up a database company, ArtBase, that builds a product for artgalleries. The core of this product is a database with a schema that capturesall the information that ggalleries need to maintain.Galleries keep information about artists, their names (which are unique),birthplaces, age, and style of art. For each piece of artwork, the artist, theyyear it was made,, its uniqueq title,, its typeyp of art ((e.g.,g, ppainting,g, lithograph,g p ,sculpture, photograph), and its price must be stored. Pieces of artwork arealso classified into groups of various kinds, for example, portraits, still lifes,works by Picasso, or works of the 19th century; a given piece may belong tomore than one group. Each group is identified by a name (like those justgiven) that describes the group. Finally, galleries keep information aboutcustomers. For each customer, galleries keep that person’s unique name,address,ddt t l amountt off dollarstotald ll spentt ini theth galleryll((veryiimportant!),t t!) andd thethartists and groups of art that the customer tends to like. Draw the ER diagramfor the database

Galleries keep information about artists, their names (which are unique),birthplaces, age, and style of art. ForF eachh pieceioff artwork,tk theth artist,ti t theth year it was made,d itsit uniqueititle,titl itsit typetof art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored.

Pieces of artwork are also classified into groups of various kinds, for example,portraits, still lifes, works by Picasso, or works of the 19th century; a given piece mayb lbelongtto more thanth one group. Each group is identified by a name (like those just given) that describes the group.

Finally, galleries keep information about customers. For each customer, gallerieskeep that person’s unique name, address, total amount of dollars spent in the gallery((veryimportant!),it t!) and the artists and groups of art that the customer tends to like

Exercise # 7 Notown records has decided to store information on musicians whoperform on their albums (as well as other company data) in a database.The company has chosen to hire you as a database designer.– Each musician that records at Notown has an SSN, a name, an address and aphonehnumber.b Poorlyl paidd musicians oftenfsharehtheh same address,ddandd noaddress has more than one phone.– Each instrument that is used in songs recorded at Notown has a name (e.g.guitar synthesizer,guitar,synthesizer flute) and a musical key (e(e.g.,g C,C B‐flat,B‐flat Eflat)Eflat).– Each album that is recorded at the Notown label has a title, a copyright date, aformat (e.g., CD or MC) and an album identifier.– Each song recorded at Notown has an id, title and an author.– Each musician may play several instruments, and a given instrument may beplayed by several musicians.– Each album has a number of songsg on it, but no songg mayy appearppon morethan one album.– Each song is performed by one or more musicians, and a musician mayperform a number of songs.– Each album has exactly one musician who acts as its producer. A producer mayproduce several albums. Draw an ER diagram for Notown.

Each musician that records at Notown has an SSN, a name, an address anda phone number. Poorly paid musicians often share the same address, andno addressddh more thanhasth one phone.h

Each instrument that is used in songs recorded at Notown has a name (e.g.guitar, synthesizer, flute) and a musical key (e.g., C, B‐flat, Eflat).

Each song recorded at Notown has an id, title and an author.

Each musician may play several instruments, and a given instrument maybe played by several musicians.

Each album that is recorded at the Notown label has a title, a copyrightdate, a format (e.g., CD or MC) and an album identifier. EachE h albumlbhhas a numberb off songs on itit, bbutt no song may appear on morethan one album.

Each song is performed by one or more musicians, and a musician mayperform a number of songs.

Each album has exactly one musician who acts as its producer. A producermay produce several albums.

The E/R Model describes a database about bands and their tours. A tour consistsof a sequence of cities visited by a band. We assume that no city is visited twiceon a single tour, and on one date, a band can visit only one city. Some of theentity sets are weak, and some of the relationships are supporting many‐onerelationships, but all double rectangles and double diamonds are not shown.Your task is to decide which of the entity sets are weak, and which relationshipssupport them. If there is a choice, prefer to use the attributes of the entity setitself in the key, and minimize the number of supporting relationships. There maybe more than one reasonable answer, especially since "tour#" can have severalreasonable interpretations. You should pick from the list below the one that ismost reasonable.

Correct Answers Stopsp is weak,, supportedppbyy On. Tours is weak, supported by By; tour# is unique only for agiven band. Tours is not weak; tour# is a globally unique IDID. Cities is weak,weak and its key is the city name and the name ofthe state it is In.

INCORRECT ANSWERS (with explanation) Stops is weak, supported by both At and On; date is not a key attribute.– We are told that a tour cannot stop at two cities on the same date. Thus, once we knowthe date and the tour that a Stop represents, we don't also need the City to have a keyfor Stops. See Sect. 2.4 (p. 54) for the rules regarding weak entity sets.Stops is weak, supported by both At and On; date is a key attribute.– We are told that a tour cannot stop at two cities on the same date. Thus, once we knowthe date and the tour that a Stop represents, we don't also need the City to have a keyfor Stops. See Sect. 2.4 (p. 54) for the rules regarding weak entity sets. Stops is weak, supported by At.– Cities is not weak.– A date and a city does not define a unique Stop (City/Tour pair). The reason is thatseveral tours may stop at the same city on the same date. See Sect. 2.4 (p. 54) for therules regarding weak entity sets.The city name by itself does not determine a unique city. There can be cities of thesame name in two or more different states,states e.g.,e g Portland ME and Portland OR.OR See Sect.Sect2.4 (p. 54) for the rules regarding weak entity sets.Bands is weak, supported by By.–A supporting relationship has to be manymany‐oneone from the entity set it supports. In orderfor this diagram to make sense, we have to assume that band names are unique,because By cannot help define a band uniquely using the weak entity set construct. SeeSect. 2.4 (p. 54) for the rules regarding weak entity sets.

States is weak, supported by In.– Stops is weak,weak and its key is the date and the name of the band makingthe tour the stop represents.– There is no many‐one relationship directly from Stops to Bands. Thus, an attribute ofBands can only contribute to the key of Stops if Tours is weak and the band nameconstributes to the key of Tours. But then, the tour# would also be in the key for Stops.See Sect. 2.4 (p. 54) for the rules regarding weak entity sets.Stops is weak, and its key is the date, the name of the associated city, andth associatedthei t d tour#.t #– A supporting relationship has to be many‐one from the entity set it supports. Note alsothat state names are unique. See Sect. 2.4 (p. 54) for the rules regarding weak entitysets.Since it is ambiguous whether tour#'s are unique, it is possible that there are two bandsthat use the same tour number, and these bands stop in the same city on the same date.See Sect. 2.4 (p. 54) for the rules regarding weak entity sets.Tours is weak, supported by On and By.–A supporting relationship has to be many‐one from the entity set it supports. Thus, Oncouldn't possibly support Tours. See Sect. 2.4 (p. 54) for the rules regarding weak entitysets.t

Exercise # 5 A company database needs to store information about employees (identified by ssn,withsalary and phone as attributes), departments (identified by dno,withdname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed byanemployee; a chldhild must be identified uniquely by name when the .

Related Documents:

1 IV. Entity Relationship Modeling 2 Entity-Relationship Model (ERM) Basis of an Entity-Relationship Diagram (ERD) A design technique Diagrams entities sets (with attributes) and the relationship between the entity sets. Recall previous definitions Entityrefers to the entity set and not a single entity occurrence E-R diagrams are the deliverablesof the

A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying relationship set. Employees ssn name lot

maximum and minimum number of relationship instances in which an entity instance can participate. E.g. cardinality is any pair of non-negative integers (a,b) such that a b. If a 0 then entity participation in a relationship is optional If a 1 then entity participation in a relationship is mandatory.

database management puts more emphasis on the extension of an entity class in a database, i.e., the current collection of instances of the entity type 3 in practice, for this chapter: \entity" \object" Entity-Relationship Model,

A relationship's degree indicates the number of associated entities or participants. Unary Relationship : single entity is associated. Binary Relationship : two entities are associated. Ternary Relationship : three entities are associated. Quandary Relationship : four entities are associated. ER Model Staff Is manager Owns Car

If minimum cardinality is 0, entity participation in a relationship is optional. If minimum cardinality is 1, entity participation in a relationship is mandatory. If maximum cardinality is 1, each instance of the entity is associated at most with a single instance of the relationship; if maximum ca

Entity User Experience 5. JustGrants User: Highlights. Key Takeaways In addition to the introduction of six foundational roles, JustGrants introduces increased visibility among users within the entity – the Entity User and their Entity Profile – allowing for more efficient user management. One Email Address One User One Entity.

Entity Management 23. 2) Select the Manage Users button in the . Doing Business As . section to open the My Apps screen for access to entity management in the Secure User Management system, DIAMD. Entity Users can only be managed by the Entity Admin. 2. Manage Users. Entity Management: Step 2