Midterm 1

Min Max Notation

(Min, Max) -Min represents participation (0 partial, >0 total) -Max represents cardinality

Key vs Superkey

- a superkey where no proper subset of the attributes is a superkey -a superkey k such that removal of any attributes from k results in a set of attributes that is not a superkey -a minimal superkey is also a key -any key is a superkey -any set of attributes that includes a key is a superkey

What kinds of degrees exist for relationship types?

-Binary: degree two -Ternary: degree three -N-ary: degree of n -In general, n-ary relationships are not equivalent to n binary relationships

High-Level Model

-Conceptual Model -How do users perceive the data? Entities (real world concepts or objects), Attributes (some prop of the entity), Relationship(association between two entities

Advantages of the DBMS Approach

-Controlling redundancy, controlling access, efficient query & update processing, backups, enforcing integrity constraints, reduced application development time, flexibility, instant updates to info

The specialization process allows us to

-Define a set of subclasses of an entity type -Establish additional specific attributes for each subclass -Establish additional specific relationship types between each subclass and other entity types or subclasses

Four Possible Constraints on Specialization

-Disjoint, total (one and only one) -Disjoint, partial (zero to one) -Overlapping, total (one to many) -Overlapping, partial (zero to many)

Single Relation Constraints

-Domain constraints, entity integrity constraints, key constraints

INSERT may violate which constraints?

-Domain constraints, key constrains, referential integrity, entity integrity

Give Examples of an entity, entity set, attribute and domain for a personnel DB

-Entity (person, employee) -Entity Set (all persons) (group of similar entities) -Attribute (name, address) -Domain (SSN 9 digit number)

Basic Operations for Changing a Database

-INSERT: a new tuple in a relation -DELETE: an existing tuple from a relation -MODIFY: an attribute of an existing tuple

Refining the ER Design

-Identity key attributes -Determine the cardinality and participation of each relationship

Representational Model

-Implementation Model -Midway between high-level and low-level

The main difference between a superclass/subclass relationship and a 1:1 relationship is:

-In a 1:1 relationship two distinct entities are related -In a superclass/subclass relationship the entity in the subclass is the same real world entity as the entity in the superclass but playing a specialized role

Requirements Collection and Analysis

-Interview users, document data requirements -Result: Data Requirements -Functional Requirements of the application

Unknown Values

-NULL value- used when value cannot be known or does not exist

When not to use a DBMS

-Overhead, speed, limited storage capacity, requirements not expected to change at all, limited data manipulation or cross referencing

Low-Level Model

-Physical Data Model -How is the data actually stored in the computer?

What is the difference between querying and updating?

-Querying: retrieving specific data from the database -Updating: change the contents of the database

Ordering of Tuples

-Relation is a set and sets have no order -Relation is not sensitive to ordering of tuples

What are the positives for using Data Modeling Tools?

-Serves as documentation of application requirements, easy user interface

3 Sections of a Class

-Top Section gives the class name -Middle section includes attributes -Last section includes operations that can be applied to individual objects

When do we use NULL?

-We don't have the value for an attribute -OR when there is no value applicable for an attribute

Referential Integrity

-a constraint defined between two relations -specify a relationship among tuples in both relations, the referencing relation and the referenced relation -pair wise relationships are used to build relationships across three or more relations

Referential Integrity Constraint

-a tuple in the referencing relation R1 having the FK references the PK of the referenced relation R2 -t1[FK] = t2[FK]

Primary Key

-an arbitrarily chosen candidate key

Relation Instance

-an ordered list of attributes -with a set of values (each tuple consists of attribute value pairs) -mapped to a domain

Computer Aided System Engineering (CASE)

-automated tools to improve the speed and quality of system development work -Maintains a repository -Uses knowledge of a particular methodology

Logical Data Independence

-change the conceptual schema without changing external schemas or application programs ( expand, change or reduce the DB)

Data Modeling Tools

-cover conceptual modeling and mapping into relational schema design

External or View Level

-describes a part of the database that a particular user group is interested in -different user, different view

Conceptual Level

-describes structure of a whole database for a community of users -hides details of physical storage -level where DB is designed and implemented -describes entities, relationships, constraints, etc

Internal Level

-describes the physical storage structure of the DB

Weak Entities

-do not have key attributes of their own -identified by being related to specific entities from another entity type -Always has total participation constraint

Partial Participation

-each entity instance need not be included at least once in the relationship -EMPLOYEES need not be assigned to any PROJECTS

Total participation

-each entity must be included at least once in the relationship -EMPLOYEES must be assigned to a DEPARTMENT

Specialization Hierarchy

-every subclass participates as a subclass in only one class/subclass relationship, results in a tree structure or strict hierarchy

Entity Relationship Model

-helpful for conceptualizing the Real World -Shows simple, static memory of a system -Includes: entity, entity set, attributes, domain

Why use a weak entity?

-lots of repeated data

Completeness Constraint

-may be total or partial -a total specialization constraint specifies that every entity in the superclass must be a member of some subclass in the specialization -partial specialization allows an entity not to belong to any of the subclasses, using a single line in EER

Uniqueness of Tuples

-no duplicate tuples in a relation

Three Schema Architecture

-promotes program data independence -multiple views of the data -each level provides mappings to the levels above and below -not always completely separate -External/View Level, Conceptual level, Internal Level

Generalization

-reverse process of abstraction -bottom up conceptual synthesis

A sublass can define

-specific attributes and specific relationship types

Overlap (o) Constraint

-specifies that the subclasses are not constrained to be disjoint -the same entity may be a member of more than one subclass of the specialization

Disjointness (d) constraint

-specifies that the subclasses of the specialization must be disjointed -an entity can be a member of at most one of the subclasses of the specialization

Specialization Lattice

-subclass can be a subclass in more than one class/subclass

Can relations contain repeating groups?

-such a relation is said to be normalized or in 1st normal form

Foreign Key Constraint

-take two relations R1 and R2 where -FK is a subset of attributes in R1 that mean the same as the corresponding PK in R2 -FK has the domain(s) of PK, but not necessarily the same names -A value of FK in a tuple t1 of R1 either occurs as a value of the PK of some tuple t2 of R2 or null

How is the type of an entity defined?

-the attributes it possesses -the relationship types which it participates

Relation

-unordered set of tuples -concrete realization, printed table does have an order -abstract construct does not

Order for Drawing the Diagram

1. Entities 2. Relationships 3. Attributes 4. Participation 5. Cardinality

3.1. Discuss the role of a high-level data model in the database design process

1.High-level data models assist in conceptual design 2.helps express data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints. 3.also used as a reference to ensure that all users' data requirements are met and that the requirements do not include conflicts.

Transaction Processing Application

A management information system designed to handle large volumes of routine, recurring transactions.

End User

A person or system that directly interacts with the solution. End users can be humans who interface with the system, or systems that send or receive data files to or from the system.

Three Tier Architecture

Application, data, UI

What are the four characteristics of transactions?

Atomic- all or nothing Consistent- don't put DB into a state that violates its constraints Isolated-occur as if they happened by themselves Durable-completed transaction can survive system failure

Main Categories of Data Models

Conceptual, Logical, Physical

Database Catalog

Consists of metadata in which definitions of database objects are stored -such as base tables, views (virtual tables), synonyms, value ranges, indexes, users, and user groups.

Discuss the capabilities that should be provided by a DBMS

Controlling Redundancy: normalization Restricting unauthorized Access Providing Persistent storage for program objects Providing storage structures for efficient query processing backup and recovery Multiple User interfaces: query language, programming language interfaces (forms and command codes) Representing Complex Relationships among Data Enforcing Integrity Constraints

ER to Relational Mapping: For each multivalued attribute

Create a new relation Primary key of R is the combination of A and FK If the multivalued attribute is composite, include its simple components

ER to Relational Mapping: For each binary M:N relationship type

Create a new relation S Include primary key of participating entity types as foreign key attributes in S Include any simple attributes of M:N relationship type

Discuss the differences between database systems and information retrieval systems

DB: data about single entity are stored in table IR: Each programs has unique set of files DB: Designed to meet the needs of multiple users & applications IR: AT mercy of information department to write manipulation programs DB: relatively complex to design, implement and maintain IR: Simple to design and implement Single application DB: process speed slow IR: processing speed faster than other ways of storing data DB: data independence. IR : Data dependence. DB Redundancy, Consistency standards of data quality. Improved sharing but can limit data sharing DB: flexibility scalability not in IR DB: reduce data redundancy. IR redundancy is big problem

How do we provide data abstraction for a database?

Data Model, this includes data types, relationships between data records and constraints on the data records

meta-data

Data associated with an object, supplementing the object's file system data.

What four main types of actions involve databases?

Database Administration Database Designing Database Users Systems Analysis and Application Programming

Schema Diagram

Displays selected aspects of schema

ER to Relational Mapping: Describe the Foreign Key Approach, Merged Approach, Relationship Relation Approach

Foreign Key approach Similarly to Many-to-One relationship. Extend the entity type that "fully" participates in the relationship. Merged approach Combine into one relation / table Relationship relation (cross reference) Create a new table of foreign keys to relate the two tables

ER to Relational Mapping: For each regular binary 1:N relationship type

Identify relation that represents participating entity type at N-side of relationship type Include primary key of other entity type as foreign key in S Include simple attributes of 1:N relationship type as attributes of S Do not need to introduce a separate relation.

ER to Relational Mapping: For each binary 1:1 relationship type

Identify relations that correspond to entity types participating in R Possible approaches: Foreign key approach Merged relationship approach Cross-reference or relationship relation approach

Database Administrator

Job title focused on directing, performing, or overseeing activities associated with a database or set of databases.

What is the difference between logical data independence and physical data independence? Which one is harder to achieve? Why?

Logical data independence is the capacity to change the conceptual schema without having to change external schemas are application programs. change conceptual schema to expand database. Only view definition and the mappings need to be changed in a DBMS that supports logical data independence physical data independence is the capacity to change the internal schema without having to change the conceptual schema, hence the external schemas need not be changed as well.

What are the different types of database end users? Discuss the main activities of each

Naive or parametric end users: those type of end users makeup a sizable portion fo database end users. constant queries and updates Sophisticated end users: engineers, scientists, businesss analysis and others. who thoroughly familiarize themselves with the facilities of dbms Standalone Users: maintain personal databases by ready-mode program packages use gui. ex) tax program

What is the difference between procedural and nonprocedural DMLs?

Procedural DML is low level DML. Must be embedded in a gneral purpose language. Individual records are objects from the database and process each speratly. Needs program language for looping etc Non-procedural DML is called high level DML. Can be used on its own to specify complex database operations. Specifies which/what data to retrieve rather than how to retrieve it. declarative languages.

Deductive Database System

Provides capabilities for defining deduction rules; inferencing new information from the stored database facts

Relation Schema

R or R(A1, A2, ..., An) -fixed set of attributes A1, A2, ..., An -each attribute Aj corresponds to exactly one of the underlying domains Dj (j=1, 2, ..., n)

Two or More Relations

Referential integrity constraints

What are the basic differences among the relational model, the object model and the XML model?

Relational: relations, same type data in columns Object: application interaction, classes methods properties of classes. closer to conceptual XML Model: Hierarchical mode. different types of data in same document. Data no inherent ordering

Database Approach

Single repository maintains data that is defined once and then accessed by various users

View Definition Language (VDL)

Specifies user views/mappings to conceptual schema

Canned Transaction

Standard types of queries and updates that have been carefully programmed and tested. Mostly used by Naive users to perform actions on the database related to their needs.

User View

Subset of the database that contains virtual data derived from the database files but is not explicitly stored.

Program Data Independence

The separation of the logical (conceptually organizes and understands data) and physical views (how and where the data are physically arranged and stored on a disk, tape, or other media) of data

Why define a subclass?

To group entities where some attributes apply to them but not all entities of the superclass

Disjointness & completeness constraints are independent

True

What is the difference between the two-tier and three-tier client/server architectures?

Two Tier: client (UI) and query server or transaction server. Upon connection transaction and query request sent using Open Databases Connectivity's API's processed server side. Application or web layer between client and database. Idea behind three tier partition roles in different layers and each layer has a task

Data Manipulation Language (DML)

Used to perform data manipulations: data addition, retrieval, insertion, deletion, and modification

Can an entity be the member of more than one subclass?

Yes

Can certain attributes may apply to some but not all entities of the superclass?

Yes

Does a member entity of the subclass represent the same real-world entity as some member of the superclass?

Yes

A superclass/subclass relationship may resemble

a 1:1 relationship at the instance level

The identifying relationship of a weak entity is

a Many-to-One relationship

Data Model

a collection of concepts that can be used to describe the structure, conceptual tools for describing, relationships, semantics, consistency constraints

Relation

a mathematical concept basaed on the idea of sets

ER to Relational Mapping Each weak entity type maps into :

a relation Introduce a foreign key that references the primary key of its identifying relation. The primary key is the primary key of its identifying relation plus its own partial key. include all simple attributes of the entity type as attributes of R Include primary key attribute of owner as foreign key attributes of R

Transaction

a set of accesses against the database

Persistent Object

a specialized object that has the property of continuous state, which means it is available at all times

Superkey

a subset of the relation attributes where all tuple values must be distinct 1. SK is a set of attributes 2. t1 and t2 are tuples 3. Then, t1[SK]/= t2[SK]

The relationship between a superclass and any one of its subclasses is called

a superclass/subclass or class/subclass relationship

Key

a value of a data item or set that uniquely identifies that row in the table

Domain

all the possible values an attribute can take -a set of atomic values, indivisible -atomic values have a name, type, format

What is sharing in context to a database?

allowing users to access the database

ER to Relational Mapping: Each simple attribute of the entity type maps to

an attribute of the relation.

Tuple

an ordered set of attributes -values derived from the appropriate domain

Database System

an organization of components that defines and regulates the collection, storage, management and use of the data in the database environment

ER to Relational Mapping: Derived attributes, by definition, can be derived, and therefore

are not necessary to be represented.

Semantic integrity constraint

based on application semantics and cannot be expressed by the model per se -express with a constraint specification language

ER to Relational Mapping: Each composite attribute will

be broken into many simple attributes.

Composite Domain

cartesian product of simple domains Ex: Month Day Year

Physical Data Independence

change the physical storage of the data without changing the conceptual schema (physical file reorganization/directory restructuring)

Catalog

complete description of the database structure and constraints

Constraint Rules

conditions on the database that ensure integrity on our data for any state of the database

View

contains virtual data derived from the database files but is not explicitly stored, allows a level of access restriction, subset of the database

Server Architecture

database architecture which system functionality: client model, server model

What is the difference between a database schema and a database state?

database schema is a description of the database and the database state (snapshot) is the database itself The schema is sometimes called the intention, and a database state is called an extension of the schema

External Schema

describe the part of the database user group sees

The same entity can have several specializations based on..

different distinguishing characteristics

UPDATE may violate

domain constraint, entity constraint, referential integrity constraint

Schema Construct

each object in the schema

Traditional File Processing

each user defines and implements the files needed for a specific software application

Attributes that exist in several entity types may be...

elevated to an independent entity type

Data Sublanguage

embedded in last language

Domain Constraint

every value in the tuple must be either an element from the domain of its attribute or null, if allowed for the attribute

Query Language

high level data manipulation language stand alone interactive manner

How is meta data stored for a DBMS?

in a catalog or dictionary

Data Definition Language(DDL)

internal and conceptual schemas

Storage Definition Language (SDL)

internal schema of the database

How doe you represent a ternary relationship as a regular entity type?

introduce an artificial or surrogate key

Deleting any entity from a superclass implies that...

it is automatically deleted from all the subclasses to which it belongs

The weak entity type must "fully" participate in

its identifying relationship

DBMS

manages the data, provides security, a means of accessing data from in the database

Cardinality

maximum number of relationship instances an entity can participate in

Degree of Relationship Type

number of participating entity types

DELETE may violate

only referential integrity

Specialization

process of defining a set of subclasses of an entity type, defined on the basis of some distinguishing characteristic of the the entities in the superclass

What is manipulating in context to databases?

querying and updating the database

Artificial Key/ Surrogate Kay

row-ids or sequential numbers are assigned as keys to identify the rows in a table

Simple Domain

set of atomic values

Database State

snapshot, data in database at a particular moment in time

Database Utility

software modules help DBA manage

Single Inheritance

some models and languages limited to single inheritance

Candidate Key

specified for each relation schema and must be unique for every tuple in any relation instance of that schema

What is defining meant for databases?

specifying what should be in the database

What is constructing in context to databases?

storing data in the database, handling file creation and organization on the disk

Multiple Inheritance

subclass with more than one superclass, if attribute or relationship originating in the same super class inherited more than once via different paths in the same lattice

Data Independence

the capacity to change the schema at one level of a database system without having to change the schema at the next higher level

Host Language

the computer language which either high level or low level data manipulation commands embedded in general purpose programming language

Database State

the data in the database at a particular moment intime

Internal Schema

the description of the physical storage

Inserting an entity in a superclass implies that...

the entity is mandatorily inserted in all applicable subclasses

Inserting an entityin a superclass of a total specialization implies that...

the entity is mandatorily inserted in at least one of the subclasses of the specialization

N-tier Architecture

the nth tier only has to know how to handle a request from the n + 1 tier and forward to h-1 tier

The members of a subclass may still share the majority of their attributes with...

the other members of the superclass

Database Scheme

the overall design

Entity Integrity Constraint

the primary key attributes of a relation schema cannot have null values in any tuple of the relation -Primary Key values are used to identify the individual tuples -t[PK]/= null for any tuple t in r(R) -if PK has several attributes, null is not allowed in any of the attributes

ER to Relational Mapping: The primary key of the entity type maps to

the primary key of the relation.

The specialization process corresponds to a...

top-down conceptual refinement process during conceptual schema design

Ternary relationships must be represented as a

weak entity type