chapter 2 database planning and database architecture
DESCRIPTION
Chapter 2 Database Planning and Database Architecture. Spring 2014. Data as a Resource. Resource: an asset that has value and incurs cost Resources include capital equipment, financial assets, personnel, and data Database is a resource because Operational data has value - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/1.jpg)
Chapter 2Database Planning and Database
Architecture
Spring 2014
![Page 2: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/2.jpg)
Data as a Resource
• Resource: an asset that has value and incurs cost
• Resources include capital equipment, financial assets, personnel, and data
• Database is a resource because– Operational data has value– Database incurs cost– Professionally managed by DBA
![Page 3: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/3.jpg)
Characteristics of Data
• Data vs. information– Data: raw facts
• Example: printout of tables as they are stored, without headings saying what they mean
– Information: processed data, useful for decision-making
• Example: formatted report using database
![Page 4: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/4.jpg)
Four Levels of Data1. Real world
• Enterprise in its environment• Mini-world, or Universe of Discourse – part of the world that is
represented in the database2. Conceptual Model
• Entities, entity sets, attributes, relationships• Often represented as ER, EER or UML diagram
3. Logical model of database-intension• Metadata, data about data• Record types, data item types, data aggregates• Schema - stored in data dictionary, catalog
4. Data occurrences-extension• Database itself• Data instances• files
![Page 5: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/5.jpg)
Four Levels of Discussing DataRealm Objects Examples
Real WorldContainingMiniworld
Enterprise
Some aspects of the enterprise
Corporation, university, bank
Human resourcesStudent enrollmentCustomers and accounts
Conceptual Model EntittyAttributeEntity setRelationship
a student, a classname, scheduleall students, all classesStudent entity relates to class entity by being enrolled in it
Logical ModelMetadata: data definitions, stored in Data Dictionary
Record type
Data item type
Data Aggregator
Student record type, Class record type
stuid, classNumber
address, consisting of street, name, state, ZIP
DataOccurrences stored in the database
Student record occurrence
Data item occurrence
File
Database
Record of student Tom Smith
‘S1001’, ‘Smith’, ‘Tom’,’History’,90
Student file with 5000 Student records
University database containing Student file, Class file, Faculty file, …
![Page 6: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/6.jpg)
Data Sublanguages
• DBMS uses a data sublanguage, with two parts– Data definition language (DDL) - used to define
the database– Data manipulation language (DML) - is used to
process the database– Data sublanguage may be embedded in a host
language-general programming language,such as Java, C, C++, C#, COBOL..
![Page 7: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/7.jpg)
Characteristics of a Conceptual Database Model
– Faithfully mirrors the operations of the organization
– Flexible enough to allow changes as new information needs arise
– Supports many different user views– Independent of physical implementation– Does not depend on the model used by a
particular database management system
![Page 8: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/8.jpg)
Stages in Database Design
• Analyze user environment• Develop conceptual data
model• Choose a DBMS• Develop logical model, by
mapping conceptual model to DBMS
• Develop physical model• Evaluate physical model• Perform tuning, if indicated• Implement physical model
Analyze User Environment
Develop Conceptual Model
Choose DBMS
Develop Logical Model
Develop Physical Model
Evaluate Physical Model
Tune System
Implement System
![Page 9: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/9.jpg)
Design Tools
• CASE (Computer-Aided Software Engineering)– tools for system analysis, project management, designing
the data model, designing applications, implementing the database, prototyping, data conversion, generating application code, generating reports, testing
• Data dictionary• Project management software
![Page 10: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/10.jpg)
Data Dictionary• Contains metadata• Can be integrated (system catalog, part of DBMS) or free-standing• Both useful for
– Collecting information about data in central location– Securing agreement on meanings of items– Communicating with users– Identifying inconsistencies – synonyms and homonyms– Keeping track of changes to DB structure– Determining impact of changes to DB structure– Identifying sources of/responsibility for items– Recording external/logical/physical models & mappings– Recording access control information– Providing audit information
• Catalog also provides audit information
![Page 11: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/11.jpg)
Project Management Software• Tools to help plan and manage projects, especially those with many
people• Include several types of charts and graphs
– GANTT chart– PERT chart
• User specifies– Scope and objectives– Major tasks and phases– Task dependencies– Resources, including personnel– Timelines
• Software can– Generate calendars– Produce graphs with different views of project– Provide means of communication for staff
![Page 12: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/12.jpg)
ID Task Name Start Finish DurationJan 4 2004 Jan 11 2004 Jan 18 2004
128 9 195 13 16 2115 2310 17 221876 11 14 20
1 4d1/8/20041/5/2004Define specifications
5 3d1/13/20041/9/2004Create E-R diagram
9 1d1/14/20041/14/2004Map E-R to relational model
10 1d1/15/20041/15/2004Normalize relational model
12 2d1/20/20041/19/2004Create EER diagram
13 1d1/21/20041/21/2004Map EER model to object-relationalmodel
14 2d1/23/20041/22/2004Create object-relational database
15 2d1/26/20041/23/2004Create UML diagram
16 1d1/27/20041/27/2004Map UML diagram to object-orientedmodel
17 2d1/29/20041/28/2004Create object-oriented database
Jan 25 2004
24 25
11 2d1/19/20041/16/2004Create relational database
26 27 28 29 30 31
8
7
6 4h1/9/20041/9/2004Identifyentities,attributes,relationships
4h1/12/20041/12/2004Identity cardinality,participationconstraints
1d1/13/20041/13/2004Draw diagram
4
3
2 2d1/6/20041/5/2004Interview users
1d1/7/20041/7/2004Identify transactions, reportsneeded
1d1/8/20041/8/2004Begin data dict, x-ref table
Resource Names
Adam
Adam, Beth
Beth,Colin
Adam
Adam, Beth
Adam, Colin
Beth
Adam
Colin
Adam, Beth, Colin
Adam
Beth
Beth
Colin
Adam
Gantt Chart for The Art Gallery Database Project
![Page 13: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/13.jpg)
Database Administrator Skills
– DBA must be• Technically competent • Good manager• Have excellent interpersonal and communication skills
– Has primary responsibility for planning, designing, developing and managing the operating database
– Database designer may do conceptual and logical design; DBA does physical design, implementation, develops, manages system
![Page 14: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/14.jpg)
Planning and Design Stage
– Preliminary planning– Identifying user requirements– Developing and maintaining the data dictionary– Designing the conceptual model– Choosing a DBMS– Developing the logical model– Developing the physical model
![Page 15: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/15.jpg)
Development Phase
– Creating and loading the database– Developing user views– Writing and maintaining documentation– Developing and enforcing data standards– Developing and enforcing application program
standards– Developing operating procedures– Doing user training
![Page 16: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/16.jpg)
Database Management Phase
• Monitoring performance• Tuning and reorganizing• Keeping current on database improvements
![Page 17: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/17.jpg)
Three-level Database Architecture
• CODASYL DBTG and ANSI/X3/SPARC - database architecture at 3 levels of abstraction
• External• Logical• Internal
– Each with written description called a schema
![Page 18: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/18.jpg)
• Rationale for separation of external and internal levels Different users need different
views of same data Users data needs may change
over time Hides complexity of database
storage structures Can change logical structure
without affecting all users Can change data and file
structures without affecting overall logical structure or users' views
Database structure unaffected by changes to the physical aspects of storage
![Page 19: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/19.jpg)
External Level
• Consists of many user models or views• Has external records - records seen by users• May include calculated or virtual data• Described in external schemas (sub-schemas)• Used to create user interface
![Page 20: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/20.jpg)
Logical Level• Entire information structure of database• “community view” as seen by DBA• Collection of logical records• Derived from conceptual model• All entities, attributes, relationships represented• Includes all record types, data item types,
relationships, constraints, semantic information, security and integrity information
• Relatively constant over time• Described in logical schema• Used to create logical record interface
![Page 21: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/21.jpg)
Internal Level
• Implementation level• Includes data structures, file organizations used
by DBMS• Depends on which DBMS used• Described in internal schema• Used to create stored record interface with
operating system• Operating system creates physical files and
physical record interface, below DB
![Page 22: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/22.jpg)
Retrieving Records in Three Level SystemUser A requests record of employee E101 through User Interface
DBMS receives request
DBMS checks under A’s external schema, external/logical mapping, logical schema in DD
DBMS checks to see if user A is authorized. If not, rejects request
DBMS checks logical/internal mapping, determines corresponding internal structures
DBMS uses stored record interface to request stored record from OS
OS identifies desired physical record and asks access method to retrieve it
Access method retrieves block of records to buffer, passes address of stored record to DBMS
DBMS checks logical/internal mapping, edits stored record, passes logical record to logical level
DBMS checks external/logical mapping, edits logical record, passes external record to User A
![Page 23: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/23.jpg)
Difference in External, Logical, Stored and Physical Record
External Employee Records:
employeeName empNumber dept
JACK JONES E101 Marketing
Logical Employee Records:empid lastName firstName dept salary
E101 Jones Jack 12 55000
Stored Employee Records:
empid lastName firstName dept salary Forward pointer
Backward Pointer
E101 Jones Jack 12 55000 10101 10001
Physical Records:
Block header rec of E90 rec of E95 rec of E101 rec of E125
![Page 24: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/24.jpg)
Data Independence
• Logical data independence– Immunity of external models to
changes in the logical model– Occurs at user interface level
• Physical data independence– Immunity of logical model to
changes in internal model– Occurs at logical interface level
External View
Logical Model
Internal Model
User Interface
User Interface
![Page 25: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/25.jpg)
Data Models
• Collection of tools for describing structure of database
• Often includes a type of diagram and specialized vocabulary
• Description of the data, relationships in data, constraints on data, some data meanings
• Most permanent part in database architecture• corresponds to conceptual level or logical level• Intension or scheme of the database• May change with schema evolution
![Page 26: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/26.jpg)
Entity-Relationship Model• A semantic model, captures meanings• Conceptual level model• Proposed by P.P. Chen in 1970s• Entities are real-world objects about which we collect data• Attributes describe the entities• Relationships are associations among entities• Entity set – set of entities of the same type• Relationship set – set of relationships of same type• Relationships sets may have descriptive attributes• Represented by E-R diagrams
![Page 27: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/27.jpg)
Basic Symbols of E-R DiagramsSymbol Name Meaning Example
Rectangle
Oval
Diamond
Line
Entity Set
Attribute
Relationship
Links:Attribute to Entity
Student
stuid
Enroll
Student EnrollEntity Set to Relationship
Attribute to Relationship
![Page 28: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/28.jpg)
Simplified E-R Diagram
Student ClassEnroll
ClassNumber
roomschedulegrade
credits
firstName
major
stuidlastName
![Page 29: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/29.jpg)
Relational Model
• Record-based model • Logical-level model• Proposed by E.F. Codd• Based on mathematical relations• Uses relations, represented as tables• Columns of tables represent attributes• Tables represent relationships as well as entities• Successor to earlier record-based models—network
and hierarchical
![Page 30: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/30.jpg)
Example Relation
Student
stuId lastName firstName major credits
S1001 Smith Tom History 90
S1002 Chin Ann Math 36
S1005 Lee Perry History 3
S1010 Burns Edward Art 63
S1013 McCarthy Owen Math 0
S1015 Jones Mary Math 42
S1020 Rivera Jane CSC 15
![Page 31: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/31.jpg)
Object-oriented Model• Similar to E-R but includes encapsulation, inheritance• Objects have both state and behavior• State is defined by attributes• Behavior is defined by methods (functions or procedures)• Designer defines classes with attributes, methods, and
relationships• Class constructor method creates object instances• Each object has a unique object ID• Classes grouped into class hierarchies• Database objects have persistence• Both conceptual-level and logical-level model• UML class diagrams often used
![Page 32: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/32.jpg)
Class Diagram
![Page 33: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/33.jpg)
Object-relational model
• Adds to relational model
– new complex datatypes
– objects with attributes and methods
– inheritance
• SQL extended to handle objects
![Page 34: Chapter 2 Database Planning and Database Architecture](https://reader033.vdocuments.us/reader033/viewer/2022061617/568151d6550346895dc00fe9/html5/thumbnails/34.jpg)
Semi-structured Model
• Collection of nodes, each with data, with different schemas
• Node contains a description of its own contents• Can be used for integrating existing databases• XML tags in documents describe structure• XML tags identify elements, sub-elements, attributes
in documents• XML DTD (Document Type Definition) or XML
Schema used to define structure