Database Quiz #2

Data Abstractions

- Classifications: for the basic modeling constructs of the ER model - Aggregation: whole-part relationships, associate entity types - Generalization: super-types - Specialization: is-a relationship for sub-types

Qualities of Conceptual Models

- Expressiveness (need a model) - Simplicity - Minimality (no redundancy) - Formality (associative)

1. Microsoft EXCEL is a good data management tool because it controls data redundancy.

- FALSE

3. DBMSs such as ORACLE RDBMS implement physical storage and access of databases. Both end users and application programs need to know the physical database schema to access the data

- FALSE, implement logical storage

5. An attribute can be decomposed into multiple attributes is a multivalue attribute.

- FALSE, multivalued attributes are attributes like major that may take on more than one value for a given entity or relationship instance

4. The conceptual data modeling should capture both data and process requirements in developing databases.

- FALSE, should not capture process requirements

2. Relational database is a good data management tool bc it organizes data as physical tables for query processing.

- FALSE, views LOGICALLY not physically

We can specify only one relationship type between two entity types

- False, they can have more then 1

Every cell in a relation can hold only a single value

- True

Every relation is a table, but no every table is a relation

- True

The primary key is used to both identify unique rows in a relation and to represent rows in relationships

- True

When developing a database system, the database is constructed during the implementation phase.

- True

Entity Type

- a collection of entities that share common properties or characteristics

Relational Database

- a database that represents data as a collection of tables in which all data relationships are represented by common values in related tables - simple structure, easy to design - support data independences - SQL - views data logically rather than physically - better control data redundancies - DBMSs

Conceptual Schema (structure)

- a detailed, technology-independent specification of the overall structure of organizational data

Database

- a non-redundant collection of LOGICALLY related records or files. the data stored in a database is independent of the computer programs that use them - a single, integrated set of files Languages - DDL and DML

Entities

- a person, place, object, an event, or a concept in the user environment about which the org wishes to maintain data. - you can't have an entity without any attributes - Anything real or abstract (role, event, Place) - shouldn't be used to capture the output

Min-Max notation

- a pilot flies in min 0 and max many flights

Crows foot notation

- a pilot flies in none or many flights

Elmasri notation

- a pilot may fly in many flights

Attributes

- a property or characteristic of an entity or relationship type that is of interest to the organization (represents things)

Unary Relationship

- a relationship between instances of a single entity type - when both participants in the relationship are the same entity (student--- Mentor) ---

Binary Relationship

- a relationship between the instances of two entity types (professor-teaches-class)

Constraints

- a rule that cannot be violated by database users

Ternary Relationship

- a simultaneous relationship among the instances (contents) of three entity types (car----BUY----sales person ---- customer)

DBMS

- a software system that is used to create, maintain, and provide controlled access to user databases - a collection of general purpose software tha facilitates the processes of defining, constructing, and manipulating a database (ORACLE) - The most popular software tools for data management - provide data protection and security control Components: query languages, facilities, integrity, backup, a data manipulation language for accessing the database. a data language used to define the structure of data

Objects

- a thing in the real world - tangible or intangible - object type: refers to a group of objects (employee is an object type of people)

Conceptual data schema

- abstraction of the reality of data elements and their relationships - conceptual view - easier to communicate with end users by using conceptual schemas

Manual Systems

- accounting book as example

Database Systems

- an application program that is used to perform a series of database activities on behalf of the users Advantages: - better data control, improve end-users interfaces, increase security, control redundancy, increase accessibility

Identifiers

- an attribute or combination of attributes whose value distinguishes instances of an entity type.

Composite attribute

- an attribute that has meaningful component parts (attributes) - an attribute that can be composed into multiple attributes - ex: address, name

Multivalued Attribute

- an attribute that may take on more than one value for a given entity or relationship instance - ex: telephone #, majors, categories - Student ---> Major

Required Attribute

- an attribute that must have a value for every entity or relationship associated

Derived attribute

- an attribute whose values can be calculated from related attributes - ex: highest bid, average bid, # of bids

Strong Entities

- an entity that exists independently of other entity types

Weak entities

- an entity type whose existence depends on some other entity type - Double rectangles -can not be uniquely described by its own attributes - owner entities and identifying relationships (can make strong by adding a unique attribute) - can have weak non-identifying (Session-register-student) session is weak.

Bottom-Up approach to database design

- analysis of existing files and applications - based on whatever database you have (may have redundancy)

Top-down approach to database design

- analysis of organizational functions - determine their information requirements - synthesis these requirements

Cardinalities

- are used to represent the min and max data instances of an entity type that can participate in a binary relationship type

Conceptual level

- core of the ANSI/SPARC framework - the model of the whole database - describes all data items and relationships between data together with constraints - captures data specification (metadata) - stable

Representation of Relationships

- create new relation - add key attributes to existing relations (foreign key approach) - specify referential integrity contraints - the best representation choice depends on the relationship type and its cardinality ratios

Information

- data that have been processed in such a way as to increase the knowledge of the person who uses the data. Quality data= quality information

Internal Level

- describes the PHYSICAL structure of the stored data - describes the mechanisms used to implement access methods - technology dependent - visible to DBMS

Conceptual Models

- entity-relationship model, object-oriented models such as UML, semantic data models

Relation

- every relation has a unique name. - every attribute must have an atomic value - every row in unique - attributes within a relation must have unique names - the order of the columns (attributes) in a relation is irrelevant - the order of rows (tuples) is irrelevant

ANSI/SPARC Framework

- foundation for database development - proposal for DBMS architecture to support data independencies - very influenctial to actual DBMS's design by vendors, especially relational DBMSs. THREE SCHEMA architecture: 1. External level 2. Conceptual level 3. Internal level

Data Warehouses

- ge uses it, multiple sources through multiple subjects

Data Model

- graphical systems used to capture the nature and relationships among data - abstraction of reality of data items - a conceptual way in organizing data items Database Models: Conceptual, Logical and Physical

Logical Models

- implementation models - Hierarchical - Network - Relational

Business Data

- inaccuracies: data doesnt reflect the reality (wrong address in database) - Inconsistency: over time duplicate records don't agree (multiple copies one might be wrong)

Values of Business Data

- increase production efficiency - enhance management control - support decision making - act as the competitive source

What about conceptual data modeling is true?

- it captures and represents data requirements at the logical level for database designers to build database systems

Characteristics of Business Data

- large volume of data - Data should be shared: it should not be owned by one entity and used for one app

Human Information Processing

- memory is limited, ability to process data is restricted, use a variety of external tools.

A car dealer hires you to design a computer-based data management solution in organizing their data about employees, cars, customers, and sales. the owner asks you to explain the difference between microsoft Excel and Access for data management, and also justify which tool is better. Explain their differences and elaborate three advantages of the database approach for data management.

- microsoft excel is a software tool that is used like a spreadsheet. the information is directly organized. You should use Excel when the database will not have many types of transactions. Excel makes it easy to store data, perform calculations and to generate output to share with others. - Access is a DBMS that is used to sort, plan, and compare data in the database. - Main Difference = Access allows for relationships between multiple tables! - Three advantages of the database approach is 1. Decreases data redundancy 2. Increases data security 3. Improved data sharing

E-R model

- most widely accepted data model for CONCEPTUAL design - easy to use/understand - capture data requirements - doesn't imply how data should be organized or implemented

Distributed databases

- multiple databases

Client/server databases

- network system

ERWIN

- non-identifying for 1:M - identifying for Weak and owner

Data independence is achieved in a FILE system because:

- none of the above, Data dependence makes the file system extremely complicated from the point of view of a programmer and database manager.

Multimedia database

- organize a variety of information sources such as voice, audio, photos etc.

Physical Models

- physical data structures (indexed files)

ORACLE designer notation

- pilot flies is flown by flight

Organizational Information Processing

- processors are humans and computers, use external memory, competitive advantages. - Easier decision making, and daily transaction processing Problems - redundancy, data control issues, poor data security, poor quality info, lack of memory

A primary key is:

- required to be unique - used to represent rows in relationships - a candidate key - used to identify unique rows

File-based processing

- sequential (in order), indexed, and direct data access. Ex: search student ID and it comes back a physical file Advantages: very efficient, relatively easy to design and implement for simple apps Issues: redundancy, bad information, lack of control over data

Conceptual data model

- shows the elements and how they are related without considering how they should be implemented - purpose is to represent data modeling - you can model process requirements into the er diagram but its no good. - they can define the database scope - the same item can be offered multiple times

Relational data Model

- simplicity - the database is perceived by users as tables - structure: relation (logical table) - data are related through the logical relationships according to data values

Physical Schema

- specifications for how data from a logical schema are stored in a computer's secondary memory by a database management system

Data

- stored representations of objects and events that have meaning and importance in the user's environment

Logical Data Independence

- the capacity to change the conceptual schema without having to change external schema or application programs

Physical Data Independence

- the capacity to change the internal schema without having to change the CONCEPTUAL SCHEMA. So, the external schemas need not be changed as well Ex: divide into more small physical tables

Object-oriented databases

information is represented in the form of objects as used in object-oriented programming.

The component of a database that makes it self-describing is the:

metadata

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 and physical data independence - Mappings between levels support data independences NEEDS: support independent end users' and applications' views of data - support program-data independence

Identifying Entity (owner)

- the entity type on which the weak entity type depends on

External Level

- the external schemas (usually many) represent different user views, each describing parts of the database - all external schemas are generated exclusively from the CONCEPTUAL SCHEMA - technology independent

Supertypes/subtypes

- the generalizations abstraction is captured by generalization hierarchies or subsets - list of constraints to capture the generalization hierarchies

Mapping

- the mapping between different levels of ANSI/SPARC architecture are applied to achieve data independencies for database systems

Identifying relationship

- the relationship between a weak entity type and its owner

Logical Schema

- the representation of a database for a particular data management technology

Representation of Entities

- two types: weak and strong - create one relation for each entity type - include all key and non-key attributes

Associative Entities

- used to represent both a relationship type and an entity type - an entity type that associates the instances of one or more entity types and contains attributes that are particular to the relationship between those entities.

There are two common conceptual data modeling issues using E-R models. You should answer this question according to the e-auction case in class

- when determining if an object is an attribute or entity we need to look at how it is being used. Also you can only explore data issues. An example we used in class was category. We decided to make it an entity because it had many attributes of its own, and it is important to the case.

The three levels of ANSI/SPARC model:

1. External 2. Conceptual 3. Internal

Types of Databases

1. Hierarchical 2. Network 3. Relational

Identify and elaborate on two types of data independencies by referring to the three-schema architecture (ANTI/SPARC)

1. Logical data independence - the capacity to change the conceptual schema without having to change external schema or application programs 2. Physical data independence - the capacity to change the internal schema without having to change the CONCEPTUAL SCHEMA. So, the external schemas need not be changed as well

Steps of logical design of relational databases

1. Refine the ER diagram (if necessary) 2. List entity and relationship types 3. List attributes and keys 4. Represent entity types as relations 5. Represent relationships 6. Specify integrity constraints 7. Apply normalization principles

Conceptual modeling process

1. identify entity types and their attributes 2. identify relationship types 3. verify the diagram and identify problems 4. identify candidate keys

SDLC

1. planning 2. Analysis 3. Detailed systems design 4. implementation 5. maintenance

Database Development Life Cycle (DDLC)

1. preliminary planning 2. database initial study (feasibility) 3. Database design 4. constuction and loading 5. testing 6. implementation 7. maintenance 8. database evolution

Database design phases

1. requirements collection and analysis 2. CONCEPTUAL DESIGN using a conceptual model (ER-model) 3. LOGICAL DESIGN using an implementation model (relational data model) 4. PHYSICAL DESIGN using different file structures (how many tables)

It may be difficult to determine if something should be represented as an entity type or a binary relationship type between two participating entity types. Quite often, you can present it either way. Explain and justify your answer with an example.

Ex: -Subject---has---Teacher---has----Student or -Subject-----teaches----student - when choosing between two participating entity types you want to look at the importance of each entity and if you can simplify the relationship so it is easier to read, and it is less complicated. Also you can look at whether or not the relationship has any unique attributes you would have to make it an entity

Data can only be accessed (retrieved) from databases by using the query language such as SQL

False

Database Design Problem

Given: a data management request and its data and application requirements Objective: develop a database system

Logical Design

Input: CONCEPTUAL SCHEMA Task: transform the conceptual schema according to an implementation data model Output: the LOGICAL SCHEMA

Physical Design

Input: the LOGICAL SCHEMA Task: specify the internal storage structure indexes, access paths, and file org for database files Output: Physical implementation structures

Requirements Collection and Analysis

Input: users data requirements Tasks: the database designers interview users to understand the data requirements Output: concisely written set of users' data requirements

Conceptual Design

Input: users' data requirements Task: analyze requirements and express them by using a CONCEPTUAL data model Output: the CONCEPTUAL SCHEMA - ER is the most popular model