(资料库系统)

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Database Systems(,資料庫系統,),9/27 10/1,2007,Lecture#2,1,Course Administration,Office Hour:,Thur,2-3,Can everyone get the textbook?,HW#1 will be on the course homepage later today,It is due 2 weeks from today.,Next week reading:,R&G Chapters 3&4.14.2,2,Chapter 2Introduction to Database Design,3,Scenario,Say if you are hired by,iBeer,Retailer as a computer consultant.,iBeer,wants you to design its database system.,How to design it?,4,Database Design,Step 1:Requirements Analysis,What data to store in the database?,What application(e.g.,queries,updates,.)needs from the database?,Step 2:Conceptual Database Design,Come up with the design:,Entity-Relation(ER)model,Sketch the design using pictures called,entity-relationship diagrams,.,Step 3:Logical Database Design,Implement the design:,relational data model,Easy to map ER diagrams into the relational data model(CH 3).,5,Requirement Analysis,Requirement analysis:,The Beer retailer wants to keep track of,Beers on shelves,Beer manufacturers:name&address,Conceptual database design,ER diagram,Logical database design:,Relational model,Beer names,台灣啤酒,青島啤酒,台灣生啤酒,Beers,Manfs,ManfBy,name,name,addr,Manufacturers names,Manufacturers addresses,台灣菸酒公賣局,台北市南昌路一段,4,號,青島啤酒廠,?,Beer names,Manufacturers names,台灣啤酒,台灣菸酒公賣局,台灣生啤酒,台灣菸酒公賣局,青島啤酒,青島啤酒廠,6,ER Model:Entity,Proposed by Peter Chen(BS NTU EE 68)in 1976.,Entity,:A real-world object distinguishable from other objects(e.g.,Joe).,An entity is described by a set of,attributes,.,Each attribute has a domain of possible values.,Entity Set,:a collection of similar entities,Each entity in an entity set is uniquely identified by a,key,attribute,.,Employees,ssn,name,(Joe,Alice,.),(123:integer),(Joe:string),7,ER Model:Relationship,Relationship,:Association among two or more entities,Joe works in finance department.,A relationship can have,descriptive attributes,.,Joe has worked in finance department since 5/2001.,Relationship Set,:Collection of similar relationships.,dname,budget,did,since,name,Works_In,Departments,Employees,ssn,(5/2001),(finance dept),(Joe),8,Ternary Relationship,dname,budget,did,since,name,Works_In,Departments,Employees,ssn,capacity,Locations,address,(Joe),(finance dept),(Taipei),9,Roles in Relationship,Reports_To,name,Employees,supervisor,ssn,subordinate,(R,oles),10,Key Constraints,Describe,at most once(entitity),relationship,Manages relationship:each department has at most one manager(okay to have none).,One department can appear,at most once,in Manages relationship set,also called,one-to-many,relation.,dname,budget,did,since,name,ssn,Employees,Departments,Manages,Joe,Alice,Mary,Peter,Finance,Accounting,Research,Legal,3/3/93,2/2/92,3/1/92,11,More Key Constraints,1-to-1,1-to Many,Many-to-Many,Women,Give Birth,Babies,Marry,Women,Men,Befriend,Women,Men,12,Participation Constraints,Describe,all(entitity),participation,relationship,Must every department have a manager?,If yes,this is a,participation constraint,All Departments entities must participate in the Manages relationship set(total participation).,lot,name,dname,budget,did,since,name,dname,budget,did,since,Manages,since,Departments,Employees,ssn,Works_In,13,Weak Entities,A,weak entity,can be identified uniquely only by considering the key of another,(owner)entity.,Pname=partial key,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.,name,age,pname,Dependents,Employees,ssn,Policy,cost,(Alicia),(2),(Hao),14,ISA(is a)Hierarchies,As in C+and OO languages,attributes are inherited from superclass.,A ISA B,every A entity is also considered to be a B entity.,Why using ISA?,Add descriptive attributes,specific(make sense)to a subclass,.,Identify entities that make sense to a relationship(policy),.,subclass entities,superclass entity,Contract_Emps,name,ssn,Employees,hourly_wages,ISA,Hourly_Emps,contractid,hours_worked,15,ISA(is a)Constraints,Overlap constraints,:Can Joe be an Hourly_Emps as well as a Contract_Emps entity?,(Allowed/disallowed),Covering constraints,:Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity?,(Yes/no),Contract_Emps,name,ssn,Employees,hourly_wages,ISA,Hourly_Emps,contractid,hours_worked,16,Aggregation,Create relationship set from relationship sets.,Aggregation,:relationship set turns into an entity set,So that they can participate in(other)relationships.,budget,did,pid,started_on,pbudget,dname,until,Departments,Projects,Sponsors,Employees,Monitors,name,ssn,since,17,Design Guideline,Avoid redundancy.,Dont use an entity set when an attribute will do.,Limit the use of weak entity sets.,18,Avoiding Redundancy,Redundancy occurs when we say the same thing in two different ways.,Redundancy is bad,wastes space,encourages inconsistency.,The two instances of the same fact