Database CIS 353
Popular in Course
Popular in Computer Information Systems
This 15 page Class Notes was uploaded by Mrs. Brenna Hills on Saturday September 26, 2015. The Class Notes belongs to CIS 353 at Grand Valley State University taught by Jamal Alsabbagh in Fall. Since its upload, it has received 39 views. For similar materials see /class/214364/cis-353-grand-valley-state-university in Computer Information Systems at Grand Valley State University.
Reviews for Database
Report this Material
What is Karma?
Karma is the currency of StudySoup.
Date Created: 09/26/15
Conceptual Modeling Using the EntityRelationship ER Model JRA ERppt AGENDA 1 Introduction 2 Major Phases in Database Design 3 Constructs of the Basic EntityRelationship ER Model 4 Discussion 5 Ternary Relationships 0 The DIA Drawing Tool l Practice Problems ltltltlt start working on them immediately JRA ERppt l1 Iriroduction Why do we need conceptual Modeling Constdertne foHthng specs A company is organized rnto departments Some departments controt protects Every empioyee Works on at ieast one protects Some emptoyees naye dependents Eyery departmentmdst naye a manager An emptoyee can Work on more tnan one project A department can be m mutttpte tocattons etc etc Now here is a database that implements the above requirements 3 MA ER ppt 3 Question Gwen the fteids are needed 0 may be by tnaterromteratton Linm tntngs tootlt good But wnat ntne apptrcatton required doze or hundreds of tabies Looks like we need a more disciplined approach See next Introduction cont A more disciplined approach requires an intermediate stage DB specs Set oftables D nia JRA ER ppt 5 Introduction cont Here s a somewhat freguent tuat n n the real wand Aiarge database system nas been in use forsome years it started quite simpie and designers nad tne design in ineimeads39 Deyeiopers iost track of its intncaCies Nowtnere is a need formodificationsadditions But nobody is sure wnere to begin The quick x Situation The better process Document tne design ngnt at tne start and update it oyertime JRA ER ppt a Introduction cont QUI 39239 Select the best answer Designing a database without a a A license b Plumbing 1 foundations d Doors e Windows f Blueprints 39239 Complete this sentence ER is to Database design like is to a large Java or CH program JRA ER ppt 7 392 Major Phases in Datdaase Design I am i we Fluum m A mm e lmn l in m w iii ms 7 AN We are here m mammum mu Won warmquott FuNcunN GEEK i mums Here we decide on what labes we need I um um mnmm mm lSltHMn 5w mmquot quotmew Nutmeg lmL D N 1 iDAriMooELJuWiMG Ln 7 W H S h Here we decide on Hm3unimmemi how o more the dais l 39 mmmuow ileum werewmwj Here we Wnie geees 1 programs WWW J RA ER ppt a 3 The ErtityRelatinnshiptER Model Tnere are severai notations forER thatexpress essentiahtythe same concepts Unhke naturai tanguage ER nas a iirnited vocaouiag tnat is suticient to descroe key teatures otaooiication domains ER neips us to think in terms of aopiication domain concegts ratnertnan irnoiernentation Being grapnicai ER neips ennance tne corngieteness ortne specs A5 tne name imoiies tne two rnaiorconstructs ottne ER modei are Entities and Relationships among entities Caution Database scnema diagrams inatsnow grannicaiy tne connections among tabe ike tnose produced by A CCESS are not ER diagrams COMPANYSyStem JRA ER ppt a nning example watw e quot 39 7 Q 2 zm f 7 ltSUPERiSKON r lt newenuawgo mum 2 An ER grim ining lin mu cowmv ammo nip niwimniniw mi mm is hilde JRA ER mat in B The main constructs of the ER model are Entity Type or Entity Set Y Type 2 Weak Enti 2 Attrbute 2 e 2 MultiValued MV Attr bute 2 Derived Computed Attr bute Relationship Type 2 Cardinality Ratio of Relationships 2 one 0 one 2 onetoman 1N 2 manytomany MN 2 Participation Constraints of Relationships 2 Total participation 2 Partial participation Next we ll look at each construct in details 8 JRA ERppt 11 B 3 Entity Type Entity Set 2 De nition A class of unique objects called entity instances in whose properties and relationships with other entities we are interes ed 2 Representation A rectangle 2 Example DEPARTMENT Attribute 2 De nition A property of an entity type 2 Representation An oval 2 Example Name of DEPARTMENT Derived Computed a 2 De nition An attribute that is not explicitly stored but rather computed n needed from stored data 2 Representation A dotted oval 2 Example Numbe rOfEmpl oye es of DE PARTMENT JRA ERppt 12 Key 2 De nition An attribute whose value is unique for every instance of the entity type 2 Representation Underlined 2 Example Name of DEPARTMENT MultiValued MV Attribute 2 De nition A property for which any entity instance can have more than alue 2 Representation A double oval 2 Example Location of DEPARTMENT Weak Entit T e 2 De nition An entity type each of whose instances has a partial key and not a regular key The instances can only be uniquely identi ed by instances of its owner entity type through an identifying relationship Representation A double rectangle Example DEPENDENT lts owner entity type is EMPLOYEE The identifying relationship is DEPENDENTSioF ERppt Relationship Type 2 De nition An association among entity types 2 Representation A diamond connecting the related entity types 2 Example WORKSiFOR 2 A relationship instance is an association among entity instances 2 Example John works for the research department 2 The degree of a relationship is the number of entity types that participate in it 2 Entity types play roles in relationships 2 Roles are implicit need not be written unlessthe relationship is recursive relates an entity type to itself see relationship SUPERVISION 2 A relationship can have its own attr butes 2 Examp e Hours in WORKSioN 2 Arelationship unl ke an entity type cannot have a key ERppt 2 Relationships have Structural constraints that are of two types 2 Cardinality Ratio 11 1NMN 2 Participation Constraints Total Partial oneto one 11 2 Example An employee can manage one department only and a department can be managed by one employee only onetomany 1 N 2 Example A department can have many employees but an employee can work for one department only manytomany MN 2 Example An employee can work on more than one project and a project can have many employees JRA ERppt 15 Total participation 2 Representation A double line 2 Example Every department must have a manager Partial participation 2 Representation A single line 2 Example Only some of the employees manage departments This ends the de nitions of ER constructs Next we ll discuss some ner issues 3 JRA ERppt 1e I4 Discussion refer to the COMPANY ER Extend the COMPANY ER subject to the following additional specs Record all the languages that an employee speaks For every year record the project s allocated budget and actual budget A location may have several projects A project is located in one location only Record for every location Location ID unique name nearest airport JRA ERppt 17 Discussion 3 Does the diagram specify whether an employee can only manage the department for which he works 2 Does the diagram specify whether an employee can only supervise employees who work for the department for which he works 2 Does the diagram specify that an employee can work only on projects that are controlled bythe department for which he works 3 If the answer is no to any one of the above questions then how would you specify it JRA ERppt 1e Discussion 2 According to the COMPANY ER 2 can an employee have two dependents that have the same name 2 Can an employee have the same name as one ofhis dependents Aweak entity type must have at least one owner entity type Why 2 The relationship between a weak entity and its owner can t be MN Why 2 The participation of a weak entity in the identifying relationship must be total 7 JRA ERppt 19 Discussion A weak entity type can have more than one owner re is an example about grade records Remember that a relationship cannot have a key Every course has a C unique name and credit hours Every student has a St unique name and address We want to keep track of who got what grade in what course amp semester and their rank in the class then eg top 5 top 10 etc Semesters are represented as F06 W07 Sp07 Su07 etc Situation1 Situation2 We want to keep a record of only We want to keep a record ofaH the latest attempt in a course attempts in a course JRA ERppt 20 Discussion A common conceptua quot mistake is to attach relationship attributes to entities instead of to relationships In the 11 relationship MANAGES in the COMPANY ER 2 Is it wrong to attach StartDate to EMPLOYEE 2 Is it wrong to attach StartDate to DEPARTMENT In the 1N relationship WORKSiFORI and suppose It had a 39Since39 attrbute 2 Is it wrong to attach smce to EMPLOYEE7 2 Is it wrong to attach since to DEPARTMENT In the MN relationship WORKSioN 2 Is it wrong to attach Hours to EMPLOYEE 2 Is it wrong to attach Hours to PROJECT What lesson can we learn from considering the above alternatives Think of conceptual clarity39 JRA ER ppt 21 5 TernaryRela ns 3 39 remaly lelaliull Hip as 1 39 y 39 39r change the meaning This ER represents who c a tuallx supplied what to whom While this ER can represent 1 Supplier1 can supply parls X and Y 2 Supplier2 can supply parls X an Project1 uses parls X and Y Supplier1 supplied parlX to project1 while Supplier2 supplied parl Y to project1 3955 Does this ER represent who supplied X orY to project1 JRA ER ppt 22 Ternary Relationships 39239 Caution2 Some design tools don t support ternary relationships sum 019 Fm quot9 a SUPPLIER supm PROJECl PART A solution is to introduce a keyless weak entity type Sname smug 1 JRA ER ppt 23 IS The DIA Draw n2 Tool I 39239 A Free drawing tool 39239 Supports ER and several other notations 39239 Available for V ndows and Linux 39239 Installed in V ndowsbased labs at GVSU look in the CIS folder 39239 Get used to it quickly 39239 Available for download 39om httpldiainstallersourceforgenetJ JRA ER ppt 24 and here are some pointers o 5 Use the participation tool when connecting entities to relationships rather than using the line tool 39239 Use the line tool to to connect attributes to entities 39239 You can rotate the cardinalities of relationships by 90 degrees in order to organize your diagram 39239 You can hide the grid and connection points for clean printing 239 A little effort will produce nice looking diagrams 00 See the next diagram and how it can be improved 8 JRA ERppt 25 Here is a reasonaby good drawing that can still be improved see red arrows JRA ERppt 25 IA 2 In order to avoid printing problems due to printer setup I suggest that you do the following 2 Right click file export by extension 2 Select the cgm format and save the drawing 2 Insert the cgm file as a picture in a Word document 2 Note other file formats work well too try a few Important Precaution 2 In addition to exporting the le into cgm format also do 2 Right click file save in orderto save the file in native format just in case you need to edit it later 0113 If all fails eg due to lack of certain le lters in your installation of MS Word you can always hide the grid and connection points in DIA capture the screen using AltePrlnt Screen and pasting into Paint JRA ERppt 27 397 Practice Problems I Here are some additional examples They are typical previous exam question Some are solved Others will be solved as a class activity It is highly recommended that you solve them individually or in groups before looking up the answers JRA ERppt 28