Lecture 2- Relational Data Model Flashcards
Why do you need a data model?
- Need a model for describing the structure of data and constraints
- And operations on data
What are the three layers in a data model schema?
- External
- Conceptual
- Physical
What is the physical level of a data model schema?
Problems working with data = routines hard coded to deal with physical representation
What are some characteristics of the physical level of data model schema?
- Diffucult to change the physical representation
- Application code becomes complex since it must deal with details
- Rapid implementation of new features impossible
Describe the conceptual level of a data model schema
- Hides details
- Presents data as a set of tables
- Mapping from conceptual to physical schema done by DBMS
- Physical schema can be changed without changing applications (refered to as physical data independence)
Describe the basic idea of relational database
- Organize data as a set of tables
- View each table as a set of rows
What are some advantages to relational databases?
- Simple
- Solid mathematical foundation (set theory)
- Powerful query languages
- Efficient query optimizers
What is a relational database?
A set of relations (tables)
A relationship consists of what?
- Instance
- Schema
What is an instance?
Table content, with rows and columns
What is a schema?
Table structure, with name and type of columns
More formally, a relation is a set of ______ or ______
- Rows
- Tuples
What is the cardinality, degree and domain of this example?
- Cardinality = 3
- Degree = 5
- Domain = name: char(16), age: {1,…,100}
What is domain?
The set of values from which the values of an attribute are drawn
Why use querying relations?
- Queries can be written intuitively and the DBMS is responsible for efficient evaluation
- Precise semantics for relational queries
- Allows the optimizer to extensively re-order operations and still ensure that the answer does not change
What is this example doing?
Creating a relations in SQL (CREATING a TABLE)
What is this example doing?
Insert a single tuple
What is this example doing?
Delete all tuples that satisfy a condition
What are integrity constraints?
Conditions that hold for any instance of the database (eg. domain constraints)
____ are defined when schema is defined
Integrity constraint
A ____ of a relation is one that satisfies all specified ICs
Legal instance (DBMS should not allow illegal instances)
Integrity constraints avoid _______
Avoids data entry errors
What is a primary key constraint?
A set of fields is a key for a relation if it is both unique (no two distinct tuples can have the same values in all key fields) and minimal (no subset of a key is a key
A relation can have ______ key
Can have more than one key
Candidate key vs primary key
- Canditate key = all keys of the relation
- Primary key = one defined by DBA
What is a superkey?
1st condition holds but the 2nd may not
A table can only have one _______
Primary key
What is a foreign key?
Set of fields in one relation that refers to a tuple in another relation
A foreign key must correspond to ______
A primary or candidate key of the other relation
Describe NO ACTION, CASCADE, SET NULL/SET DEFAULT, RESTRICT
- NO ACTION= delete/update is rejected
- CASCADE= also delete all tuples that refer to deleted tuple
- SET NULL/SET DEFAULT= set foreign key value of referencing tuple
- RESTRICT= similar to NO ACTION
Describe
- External level= applications can access data through some views, different views for different categories of users, a view is computed, mapping from external to conceptual schema is done by DBMS
- Views= a view is just a relation, but we store a definition, rather than a set of tuples, views can be dropped using the DROP VIEW command
- Views and security= views can be used to present necessary information while hiding details in underlying relations
