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
