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