Database Design Quiz 1

What should an entity type represent?

An item or noun

Logical Data Independence

The capacity to change the conceptual schema without having to change external schemas or application programs Change of a particular user's view will not affect views of other user groups. Transform the schema differences by various mappings Examples: Age, temperature, price

Physical Data Independence

The capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas need not be changed as well Examples: Learning function is programmed by humans

Can ER diagrams define the database scope?

Yes

Mappings

between levels support data independences

Is there a systematic process for conceptual data modeling?

yes

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

Fundamental Modeling structures:

Attributes Entities Relationships

Why Model Data?

Conduct a system development problem from different perspectives by using different tools Every modeling tool has its purpose: Based on certain assumptions about the aspect of the problem it is intended to solve. Help designers to collect input and seek feedbacks from users Data modeling is considered as the most important modeling technique and skill for information system development.

Conceptual Level

Core of the ANSI/SPARC framework Represents the global view of the structure of the entire database: the model of the whole database Describes all data items and data relationships together with data integrity constraints Separates data from the program (or views from the physical storage structure) Captures data specification (metadata) Technology independent Relatively stable over long period of time Visible to DBA and database designers

Database Environment

Database hardware Database Management Systems (DBMS): software package for managing databases Database users Database administrator Database: Schema (meta-data) and Instances (data contents) Database models: logical and physical Database languages (DDL and DML) Data dictionary and directory Database design tools (e.g., ERWin)

Notation of ER Diagrams

Entity types: rectangles Weak entity types: double rectangles Relationship types: diamonds Connecting lines: roles entities play in the relationships Cardinalities and connectivities

Database Design Problem

Given: a data management request and its data and application requirements Objective: develop a database system Such that: Adequately manage the data of application domains Effectively supports applications' information needs

Inaccurate data

Having $1000 instead of $10000 for customer's credit limit Having $1.05 instead of $1.50 for an item

Characteristics of Business Data

Inaccuracies: Data do not reflect the reality. E.g.: wrong address, wrong number of products on shelf Inconsistency: Over time duplicate records do not agree. E.g.: school recording two different grades for same student

Cardinalities

Indicates the number of each object type that may participate in an association.

1: 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

4: 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.

Evolution of Data Management

Manual systems (before computer age) File-based processing: sequential, indexed and direct data access

Lost data

Missing the record of a product item for sales Losing an invoice transaction

Mappings from real world primitives to ER modeling constructs: See Table 2.1

Object -> Entity Association -> Relationship Property -> Attribute

Real World Primitives

Objects Object types Properties Fact Property value set Associations Object classes

For binary associations:

One-to-one (1:1) One-to-many (1:N) Many-to-many (N:M)

Database Development Life Cycle

Preliminary Planning Database Initial Study (Feasibility Study) Database Design

Values of Properties

Properties for object types and associations Property value set: a set of possible values for a particular property.

Entity-Relationship (ER) Model

Proposed by Peter Chen in 1976 The most widely accepted data model for conceptual design Easy to use and understand by end users 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)

Database Evolution

Redesign and Reengineering Database Migration Database Integration IS30

Database Design

Requirements collection and analysis Conceptual design using a conceptual model Logical design using an implementation model Physical design using different file structures

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 Two types: Logical data independence Physical data independence

External Level

The external schemas (usually many) represent different user views, each describing portions of the database for a particular application. All external schemas are generated exclusively from the conceptual schema. Each external schema (also called view) describes the database of one application such as inventory control, customer relationship, etc. Visible to the applications and end users Technology independent

Focus on Processing Functions

The program logic and functions will be specified first. Data structure is quite simple.

Database Management System (DBMS)

A collection of general-purpose software that facilitates the process of defining, constructing, and manipulating a database for various applications E.g.,: Microsoft Access, ORACLE RDBMS, IBM DB2, Microsoft SQL Server

Databases

A single, integrated set of files

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

Objects and Object Types

An object is a thing in the real world Objects may be tangible or intangible Object type: refers to a group of objects. E.g., Employee is an object type. Each instance of an object type has certain characteristics. E.g., Employees have name, titles, salary, etc.

Top -Down

Analysis of organizational functions Determine their information requirements Synthesis these requirements

Why should we capture and represent subtype relationships?

Because the account holder could possibly do more than one action (buy or sell)

Database Development Life Cycle

Construction and Loading Testing and Evaluation Implementation Performance Maintenance and Evaluation Database Evolution

How to make sure I capture and represent all the data requirements?

Consult a partner and by prototyping

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 and integrity control

Large volume of data

Customer data for a bank: e.g., 5/3 Items and customer orders: e.g., Amazon, eBay Personal data recorded by government: e.g., social security

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 data from programs Easy to establish relationships among data Provide data security and control Provide multiple interfaces Enforce integrity constraints The most popular tool for managing data.

Problems of File-based Data Processing

Data redundancy and inconsistency Lack of Data Integration: It is the data isolation problem (Islands of Data). Each application defines and processes its own universe of data. 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 and data Inconsistent naming of terms: Homonyms and Synonyms

Data should be shared.

Data should not be owned by one entity and used for one application. Existing and new applications share the same data.

Internal Level

Describes the physical structure of the stored data (how the data is actually laid out on storage devices) Describes the methods used to implement data access (e.g., indexes, hashed addresses, etc.) Concerns with the efficiency of physical data storage and access methods 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.

Inconsistent data

Different inventory levels of an item Different grades for the same course taken

Physical Models (Physical data structures)

E.g.: Indexed files (ISAM, VSAM, etc.)

File-based Data Processing Issues

Each application has its own input and output files designed to meet its particular needs. Certain data items will appear in multiple files. Each application developed independently that will result in different representations and formats for the same data items.

'Account' & 'Category': attributes or entity types?

Entity

Is 'Bid" an attribute, an entity type or a relationship type?

Entity

Conceptual Models

Entity-Relationship (ER) model Object-oriented model such as UML Semantic data models

Three-schema architecture

External level Conceptual level Internal level

ANSI/SPARC Framework

Foundation for database development Proposal for DBMS architecture to support data independencies Very influential to DBMS's design by vendors, especially Relational DBMSs such as ORACLE

Logical Models (Implementation Models)

Hierarchical Network Relational

Databases

Hierarchical (legacy database systems) Network (legacy database systems) Relational (most popular commercial databases)

Extensions to ER Model

Incorporate a set of data abstractions to capture data requirements: Aggregation Generalization Specification Categorization

Value of Digital Data for Businesses

Increase production efficiency Enhance management control Support decision making Act as the competitive source Generate strategic value (e.g., Big Data)

3: 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.

2: 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. It is easier to understand and be used to communicate with nontechnical users. ER is the most popular model for this phase.

Associates

Interactions and relationships among objects. Associates can generate their own properties: E.g., A list of students register IS3030 taught by Professor Chiang in Fall 2017.

How to draw a 'good' and 'simple' ER diagram?

It can vary there is usually not one right answer

Is it necessary to design a database starting from the conceptual modeling?

It is up to the designer's preference

How detailed should an ER diagram be?

It should contain entities, attributes, and relationships

Human Information Processing

Memory is limited Ability to process data is restricted Use a variety of external tools

Evolution of Data Management

Multimedia database: organize a variety of information sources such as voice, audio, photos, video, etc. Object-oriented (OO) databases Client/Server databases Distributed databases (e.g., Blockchain) Data warehouses (Data Lake)

Can attributes have relationships with other attributes or entity types?

No

Can or should we combine account and customer as one entity type?

No

Can relationship types have directions?

No

Can we have an entity type without any attribute (e.g., Auction, Web site)?

No

Should a relationship type represent multiple associations (i.e., buy and sell)?

No

Should we consider implementation details during the conceptual modeling?

No

Should we model Buyer, Seller, and Bidder as separate entity types? If not, why not?

No

Should we model control flows such as "Customers create Accounts to offer items", and "Customers assign categories to items"?

No

Should we specify the 'primary keys' for the entity types?

No

Should we specify the sequence of relationship types (e.g., bid before buy)?

No

Conceptual Data Modeling Using Entity-Relationship Model

Objective: represent the data requirements using ER model

Why Conceptual Data Modeling for Database Design?

Opportunity for enhanced user participation in the design process Merits that accrue from DBMS-independent modeling Ease of understanding of the big picture and the consequent facilitation of maintenance of schemas and applications in the long run

Organizational Information Processing

Processors: humans and computers Use external memory Adopt external sources as processors

Common Problems of Organizational Memory

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

Bid, Buy & Sell: Relationship or Entity?

Relationship

Conceptual Data Model:

Shows the data elements and how they are related without considering how they should be implemented.

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

Organizational Memory

Storage medium: most data is stored in memory Storage structure: File and Database Data entry and information retrieval methods Trade-off between access speed and cost Essential component of modern organizations Key competitive resources

Needs of Data Independences

Support independent end users' and applications' views of data Support program-data independence Minimize the ripple effort of changes: E.g.: application programs immune to changes in storage structure and access methods But, how to achieve them?

Components of a DBMS

The major components of this include one or more query languages; tools for generating reports; facilities for providing security, integrity, backup and recovery; a data manipulation language for accessing the database; and a data definition language used to define the structure of data.

Integrated

The responsibility for 'integrating' data items as needed is assumed by the DBMS instead of the programmer.

Self-describing

The structure of the database (metadata) is recorded within the database system - not in the application programs.

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

Data Models

What is a model? A representation of something real for design purposes Examples: blueprint, prototype, simulation What is the purpose of models? So designers know what they need to change What are data models and their purposes? Abstraction of reality of data items A conceptual way in organizing data items

Can relationship types have their own attributes?

Yes

Can we have more than one relationship type between two entity types? (e.g., Customers and Items)

Yes

Should (can) I make assumptions for the conceptual data modeling?

Yes

Should we model the 'derived' attribute such as '# of bidder', 'the highest bid'?

Yes

Should we model the business rules such as "A bidder cannot bid his/her own items."

Yes

Can we model process requirements into the ER diagram? If we can, do we want to do so?

Yes, but we don't want to

Can attributes have their own attributes?

Yes, they are known as composite attributes

Information

a collective of data that holds meaning

What is the purpose of conceptual modeling?

comprehension, abstraction, representation

Data

material that can be accessed that has no specific meaning

What to do and how to start the conceptual data modeling process?

start with an ER diagram