Database Design Mid Term
Conceptual Data Schema
-Abstraction of the reality of data elements and their relationships -conceptual view of the data requirements -pictorial representation of the data requirements -it is easier to communicate with end users by using conceptual schemas
Bottom-Up
-Analysis of existing files and applications -Synthesis programs and algorithms
Top-Down
-Analysis of organizational functions -Determine their information requirements -Synthesis these requirements
Conceptual Level
-Core of ANSI/SPARC framework -represents the global view of the structure of the entire database: model of database -describes all data items and relationships between data together with integrity constraints -separates data from the program -captures data specifications (metadata) -technology independent -relatively stable over long period of time -visible to DBA and database designers
Why maintain organizational memory
-Daily transaction processing -Decision making -Competitive advantages
Why Database approach?
-Data is the valuable resource, not the hardware -Data should be shared and accessed as needed -Data should occupy the central position -one representation for each piece of data -control data redundancy -separate from programs -easy to establish relationships among data items -provide data security and control -provide multiple interfaces -enforce integrity constraints -most popular tool for managing organizational data
Database Development Life Cycle
-Database Initial Study (feasibility study) -Database Design -Implementation and Loading -Testing and Evaluation -Operation -Maintenance and Evaluation
Associates
-Interactions and relationships among objects -associates can generate their own properties Ex. list of students register IS330 taught by Professor Chiang at Fall 2017
Common Problems of organizational Memory Systems
-Redundancy -Data control issues -Poor user interface -Poor data security -Poor information quality -Long delays in responding to information needs -unable to answer questions about the real world: lack of reality -lack of memory (data) integration
the basic structure: relation
-a two dimensional table -rows correspond to individual records and data relationships -rows are called "tuples" -columns correspond to attributes (properties) -each attribute has a data domain specified
What are data models and their purposes?
-abstraction of reality of data items -a conceptual way in organizing data items
Entity
-anything real or abstract -should not be used to capture the output -described by a name with a set of attributes -strong entities vs weak entities
Weak Entities
-can not be uniquely described by its own attributes -owner entities and identifying relationships -several level of weak entities can be specified -owner entity type is a weak entity type
Types of Data Models
-conceptual models -logical models -physical models
Advantages of Database Systems
-control data redundancy -eliminate data inconsistencies -facilitate data sharing facilitate better data management -increase application reliability -increase data accessibility -better data control and protection -improve end-user interfaces -reduce maintenance work -increase security, privacy, & integrity control
Possible representations of relationships
-create a new relation -add key attribute(s) to existing relations. Foreign key approach -specify referential integrity constraints
Problems of file-based data processing
-data redundancy & inconsistency -lack of data integration -difficult to locate and access data -difficult to ensure data consistency -lack of control over data -lack of flexibility - Ad hoc data queries to the applications are impossible -cost of software development is high: excessive programming effort -dependence between programs & data -inconsistent naming of terms
Internal Level
-describes the physical structure of the stored data -describes the mechanisms used to implement access methods -concerned with the efficiency of data storage and access mechanisms -technology dependent -visible to DBMS and particularly to DBA
Relational Data Model
-developed by E.F. Codd at IBM in 1970 -conceptual simplicity -the database is perceived by users as tables -basic structure: relation (logical table) -data are related through the logical relationships according to the data values -users are isolated from complex implementation details inside the database systems
Conceptual Model
-entity relationship model -object oriented model such as UML -semantic data models
Definition of Relation
-every relation has a unique name -every attribute must has atomic value -every row is unique -attributes within a relation must have unique names -the order of the columns (attributes) and rows (tuples) in a relation is irrelevant
ANSI/ SPARC Framework
-foundation for database development -proposal for DBMS architecture to support data independencies -very influential to actual DBMS's design by vendors, especially relational DBMSs - 3 schema architecture
Database Environment
-hardware -Management Systems (DBMS): software package for managing databases -users -administrator -schema (med-data) and instances (data contents) -models (logical and physical) -languages (DDL and DML) -data dictionary and directory -Design tools
Value of business data
-increase production efficiency -enhance management control -support decision making -act as the competitive source -strategic asset (Big Data)
Costs of incorrect and incomplete data
-losing a data item (losing an invoice transaction) -inaccurate data (having $100 instead of $10000) -inconsistent data (different levels of inventory)
Real World Primitives
-objects -object types -properties -fact -property value set -associations -object classes
Components of a DBMS
-one or more query languages -tools for generating reports -facilities for providing security, integrity, backup and recovery -data manipulation language for accessing the database -a data definition language used to define the structure of data
Entity Relationship (ER) Model
-originally proposed by Peter Chen in 1976 -most widely accepted data model for conceptual design -easy to use and understand -capture and represent data requirements -doesn't imply how data should be organized and implemented -chosen by ANSI in 1988 as the standard model for Information Resource Directory Systems (IRDSs)
Focus on file-based data processing functions
-program logic and functions will be specified first -data structure is quite simple
Object Type
-refers to a group of objects -each instance of this has certain characteristics Ex. employees have names, titles, etc
Relationship
-represent association among entities -grouped into relationship types
External Level
-represent different user views, each describing portions of the database -all are generated exclusively from the conceptual schema -each describes the database of one application like inventory control, customer relationship, etc -visible to the applications and end users -technology independent
Attribute
-represent properties of entities and relationships -identifiers versus descriptor attributes -identifiers (candidate keys) -composite attributes (ex. address, name) -multivalued attributes (ex. telephone #, majors) -derived attributes (ex. highest bid, average bid)
Why Relational Databases?
-simple structure, easy to design -support data independences -powerful and flexible data manipulation capability -4GL query language (SQL) -view data logically rather than physically -better control data redundancies
Needs of data independences
-support independent end users' and applications' view of data -support program-data independence -minimize the ripple effort of changes
Logical Data Independence
-the capacity to change the conceptual schema without having to change external schema or application programs -change of a particular user's view will not affect other groups -transform the schema differences by various mappings
Physical Data Independence
-the capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas won't need to be changed. Ex. Fifth Third Customers
Object
-thing in the real world -may be tangible or intangible
Representation of entities
-two types: strong and weak entity -create one relation for each entity type -include all key and non-key attributes
File-based data processing advantages
-very efficient use of machine resource, based on the view that computing power is the most valuable resource -relatively easy to design and implement for simple applications
Conceptual Modeling Process
1. Identify entity types and their attributes 2. Identify relationships 3. Verify the diagram and identify problems 4. Identify candidate keys
Database Design Phases
1. Requirements Collection and Analysis 2. Conceptual Design 3.Logical Design 4. Physical Design
Types of data independence
1. logical 2. physical
Evolution of Data Management
1. manual systems (before computer age) 2. File-based processing 3. Databases (Hierarchical, network, relational) 4. Multimedia database 5. Object-oriented (OO) databases 5. Client/Server databases 6. Distributed databases 7. Data warehouses
Steps of logical design of relational databases
1. refine the ER diagram (if necessary) 2. List entity and relationship types 3. list attributes ad keys 4. represent entity types as relations 5. represent relationships 6. specify integrity constraints (e.g. referential integrity constraints) 7. apply normalization principles
Database Management System (DBMS)
A collection of general-purpose software that facilities the process of defining, constructing, and manipulating a database for various applications EX. Access, Oracle, IBM DB
Databases definition
A non-redundant collection of logically related records or files. It consolidates many records previously stored in separate files so that a common pool of data records serves many data processing applications. The data stored in a database is independent of the computer programs that use them.
What is a Database?
A self-describing collection of integrated records
What is an associate (composite) entity type? Provide and justify an example
An associate entity is an entity that also acts as a relationship.
Which of the following, (a)-(c), is not a true statement of relational database systems (a) DBMSs are the most popular software tools for data management (b) Data can only be accessed (retreived) from databases by using the query language such as SQL (c) DBMSs provide data protection and security control (d) None of the above
B
What is a model?
Design of something can change only captures certain realities Ex. Prototype
Three schema architecture
External Conceptual Internal
A relational data model is a physical implementation model of database systems. T/F
F
A relational data model is an implementation (ie., physical) model of database systems. T/F
F
Non-identifying relationships are assigned between weak entity types and their owner entity types. T/F
F
We should assign cardinality ratios to the ternary relationship. T/F
F
We should specify the primary key for each entity type during the conceptual data modeling using ER model. T/F
F
An attribute can be decomposed into multiple attributes is a multivalue attribute. T/F
False
DBMSs such as Oracle RDBMS implement the physical storage and access of databases. Both end users and application programs need to know the physical database design (schema) to access data. T/F
False
Microsoft EXCEL is a good data management tool because it controls data redundancy. T/F
False
Relationship database is a good data management tool because it organizes data as physical tables for query processing. T/F
False
The conceptual data modeling should capture both data and process requirements in developing databases. T/F
False
Types of Information Processing
Human Information Processing Organizational Information Processing
Logical Model
Implementations models -Hierarchical -Network -Relation
Characteristics of business data
Inaccuracies Inconsistency Large volume of data data should be shared
Requirements Collection and Analysis
Input- application knowledge and users information needs Task- The database designers interview users to understand and document their data requirements Output- concisely written set of users data requirements
Logical Design (Data Model Mapping)
Input- conceptual schema Task- Transform the conceptual schema according to an implementation data model Output- the logical database schema Relational data model is the most popular implementation data model of commercial DBMSs
Physical Database Design
Input- the logical database schema Task- specify the internal storage structure indexes, access paths, and file organizations for database files Output- physical implementation structures It is fully dependent on the target DBMS
Conceptual Design
Input- users data requirements Task- analyze users requirements (business rules) and express them by using a conceptual data model Output- the conceptual schema The conceptual schema doesn't include implementation details. Its easier to understand and used to communicate with nontechnical users ER is the most popular model for this phase
Human Information Processing
Memory is limited. Ability to process data is restricted. Use a variety of external tools
Binary relationship type
Most common connects 2 entities
An attribute that contains more than one value is represented as a ________ attribute in ER diagrams
Multivalue
N- ary relationship type
Number of entities connected is higher than 3
Unary relationship type
One entity attached
Systems Development Life Cycle
Planning Analysis Detailed Systems Design Implementation Maintenance
Organizational Information Processing
Processors: humans and computers. Use external memory. Adopt external sources as processors
Best representation choice of relationships depend on what?
Relationship type and its cardinality ratios
Organizational Memory System
Storage structure: file and databases Data entry and information retrieval methods Trade-off between access speed and cost Essential component of modern organizations Key competitive resources
A weak entity type can be the owner entity of another weak entity type. T/F
T
Attributes of a relation should contain atomic values. T/F
T
The database systems for data management support the control of data redundancy to ensure data consistency. T/F
T
The foreign key of a relation, R, can also be the candidate key of the relation, R. T/F
T
We can create a new relation to represent an one-to-one (1:1) binary relationship type during the logical design of relational databases. T/F
T
How is a database integrated?
The responsibility for 'integrating' data item as needed is assumed by the DBMS instead of the programmer
How is a database self-describing?
The structure of the database (metadata) is recorded within the database system- not in the application programs
Approaches to Database Design
Top-Down Bottom-Up
property value set
a set of possible values for a particular property
The three modeling constructs of Entity Relationship model are ____ entity and relationship
attribute
____ are used to represent the minimal and maximal data instances of an entity type that can participate in a binary relationship type
cardinalities
An attribute that can be broken down into several attributes is called a ____ attribute
composite
Ternary relationship type
connects 3 entities Ex. Car, salesman, customer are connected through a sale
A relationship type in an ER diagram can represent an association of (a) two or three entity types (b) more than three entity types (c) entity type and relationship type (d) a and b (e) a, b, and c
d
Data independence is achieved in a file system because (a) the ANSI/SPARC architecture is adopted (b) application programs are independent of the physical data structure (c) the physical data structure can be converted according to the needs of different application programs (d) none of the above. system.
d
The number of entity types that participate in a relationship type is called the _______ of the relationship type
degree
What is the purpose of conceptual modeling?
design a representation of data requirements
Which of the following (a)-(d), is not a true statement of conceptual data modeling using the ER data model. (a) we can specify only one relationship type between two entity types (b) an entity type may not have any attribute (c) relationship types can have their own attributes (d) there are more than one acceptable ER diagram for each conceptual modeling case (e) (a) and (b) (f) (a), (b), and (c)
e
which of the following, (a)-(c), is a true statement of the conceptual data modeling (a) it attempts to capture and represent database implementation details (b) it captures and represents data requirements at the logical level for database designers to build database systems (c) conceptual schemas such as ER diagrams can enhance the communication with end users during database design (d) a and b (e) b and c (f) a and c
e
Understanding the steps involved in transforming ER diagrams into the relational data schemas is important because (a) There may be several possible logical design choices (b) You will be able to verify the logical design generated by a database design tool such as ERWin (c) You can modify SQL DDL statements in creating the database in the target DBMS according to the logical design requirements (d) It will help you as a database designer to redesign an existing database to improve its performance (e) a and b (f) a, b, c, and d
f
Which of the following (a)-(c) is true about the foreign keys (a) a foreign key specifies the referential integrity in a relational data model (b) a foreign key can be used to represent a many-to-many binary relationship type (c) a foreign key cannot have the unique values (d) a and b (e) a, b, and c (f) none of the above is a true statement about foreign keys
f
Which of the following (a)-(d) are properties about relations of relational data model (a) attributes must contain atomic values (b) no two rows in a relation are identical (c) Attributes of a relation cannot have the same name (d) the order of columns and rows in a relation is relevant (e) a and b (f) a, b, and c (g) a, b, and d
f
The _______ data abstraction depicts a relationship between an entity type and several entity types
generalization
Cardinalities
indicates the # of each object type that may participate in an association Ex. Binary association (1:1, 1:M, etc)
Three levels of ANSI/ SPARC model are: external, conceptual, and ____________
internal
The _________ between different levels of the ANSI/SPARC architecture are applied to achieve data independences for database systems.
mappings
Identify the four phases of database design. Discuss input, task, and output of each phase
na
What are the possible ways to present an 1:1 binary relationship in the logical design of a relational database? provide example
na
What is a ternary relationship? Provide an example and justify why it should be like this
na customer, dealer, car
The _______ data independence is the capacity to change the internal schema without having to change the conceptual schema
physical
The four phases of database design are _______, conceptual data modeling, logical design, and physical design
requirements and analysis
Examples of File-based processing
sequential, indexed, and direct data access
Data Independences
the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. Mappings between levels support this
A relationship between the instances of a single entity type is called an ____________ relationship type
unary
