cs157blecture1

cs157blecture1内容摘要：

are used by the anization  Results: Metadata identified, Data Dictionary, Conceptual Model ER diagram 169。 Silberschatz, Korth and Sudarshan Database System Concepts Database Design Process  Requirements Formulation and analysis  Systems Analysis Process  Examine all of the information sources used in existing applications  Identify the characteristics of each data element – numeric – text – date/time – etc.  Examine the tasks carried out using the information  Examine results or reports created using the information 169。 Silberschatz, Korth and Sudarshan Database System Concepts Database Design Process  Conceptual Model  Merge the collective needs of all applications  Determine what Entities are being used  Some object about which information is to maintained  What are the Attributes of those entities?  Properties or characteristics of the entity  What attributes uniquely identify the entity  What are the Relationships between entities  How the entities interact with each other? 169。 Silberschatz, Korth and Sudarshan Database System Concepts Database Design Process  Logical Model  How is each entity and relationship represented in the Data Model of the DBMS  Hierarchic?  Network?  Relational?  ObjectOriented? 169。 Silberschatz, Korth and Sudarshan Database System Concepts Database Design Process  Physical (AKA Internal) Model  Choices of index file structure  Choices of data storage formats  Choices of disk layout 169。 Silberschatz, Korth and Sudarshan Database System Concepts Chapter 2, Representing Information with Data Models  Entity Relationship (ER) Model  highlevel, conceptual data model  Specify conceptual schema  conceptual database design  Identify the data requirements of users and detailed descriptions of data types, relationships and constraints.  Concentrate on specifying the properties of the data, not storage. 169。 Silberschatz, Korth and Sudarshan Database System Concepts An Example of ER Modeling  Company database  Department  name, number, manager (employee), start date of manager  Projects controlled by department  name, number, single location  Employees  name, ssn, address, salary, sex, birthdate  assigned to department, several projects  Dependents of employees 169。 Silberschatz, Korth and Sudarshan Database System Concepts Principals of ER Modeling  Entities and classes  Entity, a thing in the real world  Entity Class, the structure of a collection of similar entities  Attributes  Attribute, a property of an entity  Each entity has a value for each of its attributes  Types of attributes  simple vs. posite, singlevalued vs. multivalued, stored vs. derived  domains of attributes 169。 Silberschatz, Korth and Sudarshan Database System Concepts Relationships Between Entities  Relationship type defines a set of associations among given types.  Relationsip Instances are particular relationships among objects.  Examples of relationship types in pany database  Manages: 1:1 between employee and department  Worksfor: 1:N between department and employee  Controls: 1:N between department and project 169。 Silberschatz, Korth and Sudarshan Database System Concepts Relationships, Roles, and Structural Constraints  Roles are attributes that signify the function of a particular entity (type) in a relationship  Employee manages department  Department is managed by employee  Employee worksfor department  Department has employees who work for it  Constraints can be  cardinality  Each department can have no more than one manager  participation  Each department must have a manager 169。 Silberschatz, Korth and Sudarshan Database System Concepts ER schema diagram for Company 1E m p l o y e e1D e p e n d e n t1MD e p e n d sOnM a n a g e sW o r k sF o rP ro j e c tC o n t r o l sD e p a r t m e n tW o r k sOnS u p e rv i s e sM169。 Silberschatz, Korth and Sudarshan Database System Concepts Entity Classes for BigHit Video Entit y Cl ass Descr i pt i onCust om e r A c ust o m e r o f t he busine s sVi deot ape An it em i n t he r ent al i nv ent or yEm pl oy ee A per son w ho w or k s i n one o r m or est or esPay Stat em ent A r eco r d o f t he w ag es pai d t o anem pl oy eeT i m eCa r d A r eco r d o f a bl oc k o f t i m e w or k ed by anem pl oy ee at a s t or eStor e O ne o f t he r e t ai l out l et s o f Bi g Hi t Vi deoRental T he r en t al o f a v i deot ape by a cus t om erf or a spe ci f i c pe r i od and c ostPurc hase O r de r A r e q ues t t o pu r chase an i t emVendor A c om pany t hat sell s i t em s t o Bi g Hi tVi deo169。 Silberschatz, Korth and Sudarshan Database System Concepts Sample Attribute Specifications A t t rib u t e T yp e Do ma i n o f va l u es De sc rip t i o ntitle stri n g u n b o u n d ed T h e t i t l e o f an i t eml as t Na m e stri n g 3 0 ch ara cters T h e l as t n am e o f a p ers o nf i rstNa me stri n g 3 0 ch ara cters T h e f i rst n a m e o f a p ers o nssn stri n g 1 0 d i g i t s A so cial se cu ri t y n u m b erac co u n t Id n u m b er 4 b y t e i n t eg er T h e i d en t i f i er o f a cu sto m erac co u n to t h erUs ers se t se t o f stri n g s o f 3 0 ch ara cters Na m es o f o t h er p eo p l eau t h o r i ze d t o u se t h i sac co u n tn u m b erRen t alsn u m b er 4 b y t e i n t eg er Nu m b er o f ren t als f o r acu sto m erad d res s co m p o sit e 2 stri n g s o f 3 0 ch ara cters, o n estri n g o f 2 ch ara cters, an d o n estri n g o f 9 d i g i t s.A n ad d res s t h at co n sist s o f astreet, cit y , state an d zip co d e169。 Silberschatz, Korth and Sudarshan Database System Concepts Entity Classes, Attri。