UVA DEPARTMENT OF COMPUTER SCIENCEER-1Data Modeling using ER ModelDatabase design process- requirements collection and analysis:database requirements and functional requirements- conceptual DB design using a high-level model:easier to understand and communicate with others- logical DB design (data model mapping):conceptual schema is transformed from a high-leveldata model into implementation data model- physical database design:internal data structures and file organizationsfor DB are specified- application programs and transactions are designedand implemented according to the high-level specificationUVA DEPARTMENT OF COMPUTER SCIENCEER-2Entity-Relationship (ER) ModelEntities and attributes- entity: an object in the real-world with an independentexistence (either physical or conceptual)- entity type defines a set of entities with the sameproperties: people, buildings, departments- attributes: a set of properties that describes the entityattributes (people) = age, color, SSN, address ...- a particular entity has a value for each of its attributes- distinction between entities and attributes dependsentirely on the context<ex> color may be an entity with attributes name and freq- an entity is represented by the values of its attributes- key attribute of entity type: unique value for an entityUVA DEPARTMENT OF COMPUTER SCIENCEER-3ER ModelRelationships- an association among several entitiesStudentsCoursesenrollmentManagersmanagesEmployees- binary relationships: only 2 entity sets involved- mapping cardinality:one-to-one (1:1), one-to-many (1:N), many-to-many (N:M)enrollment: many-to-many manages: one-to-many- function: one-to-many binary relationship- N:M relationship is not a function, difficult torepresent in some logical modelsUVA DEPARTMENT OF COMPUTER SCIENCEER-4ER DiagramsStudentsCoursesenrollmententity: rectanglesrelationship: diamondsattributes: ellipsesNameMajorSSNYearTermCourse#C.descriptionSemesterUVA DEPARTMENT OF COMPUTER SCIENCEER-5ER DiagramsCustomerCustAccAccountNameStreet CityNumber BalanceC1C2C3Entity setr1r2r3a1a2a3Relationship set Entity setM:N relationship CustAccA customer can have several accountsAn account can be owned by several customers.UVA DEPARTMENT OF COMPUTER SCIENCEER-6Representing RelationshipsRelationships in ER model should not be confused with relationsin relational modelRelational model- as a relation- uniformity: both data (entity sets) and relationshipsare represented by a collection of tables (relations),each having # of columns with unique namesNetwork model- link between records- data records are organized as collections ofarbitrary graphsHierarchical model- link between records- data records are organized as collections of treesUVA DEPARTMENT OF COMPUTER SCIENCEER-7Representing RelationshipsCustomerCustAccAccountNameStreet CityNumber BalanceRelationalLowery Name Street NumberMapleCityNY900ShiverNorthDC555ShiverNorthDC667HongSunnyCheville 801HongSunny Cheville667NumberBalance900 1055555 1000667 2000801100UVA DEPARTMENT OF COMPUTER SCIENCEER-8Representing RelationshipsCustomerCustAccAccountNameStreet CityNumber BalanceNetworkLowery Maple NYShiver North DCHong Sunny Cheville900 1055555 1000667 2000801 100HierarchicalLowery Maple NY Shiver North DCHong Sunny Cheville900 1055555 1000667 2000801 100667 2000One tree for each customerMany-to-many relationships are handled by duplicating recordsUVA DEPARTMENT OF COMPUTER SCIENCESystem-1System Structure of DBMSDivide and conquer- DBMS is a complex software system, need to be partitionedinto components to deal with each of the responsibilities- DB manager, file manager, compilers, query processor- OS provides the most basic services: overheadDatabase manager- handles database access at run-time, enforcing integrity- scheduling, concurrency control, recovery, cache manage- access to disk goes through file managerFile manager (stored data manager)- control access to DBMS information stored on disk:data files and meta-data (data dictionary)Compilers and query processor- compiers for DDL and DML, and interactive queriesUVA DEPARTMENT OF COMPUTER SCIENCESystem-2DBMS Interfaces and User GroupDifferent user groups use different interfaces- naive (parametric) users: canned application programssuch as automatic teller machine (min. keystrokes)- casual users: query formulation usingDML, menu/form-based interfaces, graphical interfaces- application programmers: use DML and API embedded inhost languages- DBA (database administrator) staff: use DDL statementsand privileged commands to construct and maintainDB schemas, and to control database manager and filemanagers during run-timee.g., creating accounts, setting system parameters,granting authorization, changing schemas.UVA DEPARTMENT OF COMPUTER SCIENCESystem-3Database Administrator (DBA)The group or person responsible for proper and efficientoperation and use of the databaseFunctions of DBA- schema definition- storage and access methods definition (physical schema)- access authorization and control- integrity constraints specification- data validation- performance monitoring and restructuring if
View Full Document