CSC 3300 Database Management Systems Exam 1
Database System Drawbacks:
Data Redundancy
Multiple File Formats
Duplication of information across multiple files
Data Access Difficulties
Need to write a new computer program to accomplish each new task
Data Isolation
Multiple files and formats
Data Integrity
Integrity constraints become buried in program code rather than stated explicitly.
Hard to add new constraints or change existing ones.
Atomicity Problems
Failures may leave database in an inconsistent state with partial updates carried out.
Concurrency Control
It is needed for performance, but uncontrolled concurrent access can lead to inconsistencies.
Security
Hard to provide a user to access to some, but not all, data.
Abstraction Levels of Database Management Systems
Physical Abstraction (Internal Level):
Describes how a record is stored on the file system.
Logical Abstraction (Conceptual Level):
Describes how data is stored in the database and the relationships among the different data points.
View Levels (External Levels)
Application programs hide details of data types.
Also hide information for security purposes.
Instances and schemas are similar to:
Types and variables in programming languages.
A schema is:
The logical structure of the data base
Physical schema: Database design at the physical level.
Logical Schema: Database Design at the logical level
An instance is:
The actual content of the database at a particular point in time
Physical Data Independence:
The ability to modify the physical schema without changing the logical schema.
Applications depend on the:
logical schema
A data model is:
A collection of tools for describing data, data relationships, data semantics, and data constraints.
Relational Model:
ER Model (slides 20 and 21):
Data Definition Language (DDL):
Specification notation used for defining the structure of the database.
The DDL compiler generates:
A set of tables stored in a data directory.
Metadata is:
Data about data
Data Manipulation Language (DML):
Language for accessing and manipulating the data organized by the appropriate data model.
DML is also known as a:
Query Language
Procedural/Imperative Languages:
User specifies what data is required and how to get to the data.
Declarative Languages:
User specifies what data is required without specifying how to get to the data.
SQL is the most:
Widely used query language
Integrity Constraints:
Primary Key
Referential Integrity (constraints in DML)
Authorization
SQL query format:
select <attributes>
from <tables>
where <table.attribute>= value
Logical Design of a database:
Deciding on the database schema
What database will have a good collection of relation schema.
Physical Design of a database:
Deciding on the physical layout of the database
Entity Relationship (ER) Model:
Models an enterprise as a collection of entities and relationships.
Object Relational Data Models
Relational Models
Flat “Atomic” Values”
Atomic data is:
Indivisible and irreducible
Atomic transactions ensure that a transaction either occurs or is discarded.
Object Relational Data Models
Extend the relational data model by including object orientation and constructs to deal with added data values.
Allow attributes of tuples to have complex types, including non-atomic values such as nested relations.
OR Models provide:
upward compatibility with existing relational languages.
Types of consistency constraint:
Domain
Referential
What are the components of a relational
Relational schema
Relation
Table
Record
Tuple
Row
Storage Manager
A program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
Storage Management is responsible for:
Interaction with the file manager
Efficient storing, retrieving, and updating data
Storage Management Issues:
Storage Access
File Organization
Indexing and Hashing
Components of a typical DBMS:
Components of a Storage Manager:
Authorization and integrity
Transaction
File
Buffer
Data Dictionary
Indices
Components of a Query Processor:
DDL Interpreter
DML Compiler
Query optimizer
Query Evaluator
Query plan
Terms for transaction management:
Atomicity
Consistency
Integrity
Durability
Transaction
Functions of Database Architecture (DBA):
Schema definition
Controlling Access
Maintenance
A relation is represented as a:
Table
A tuple is represented as a:
Row in a relation
Attributes is represented as a:
Column in a relation
The domain of the attribute is:
The set of allowed values for a specific attribute.
Attribute values are normally required to be:
Atomic
The value null is:
A member of every domain
The current values of a relation are specified by a:
Table
Keys:
Superkeys
If the values for the key are sufficient to identify a unique tuple of each possible relation
Candidate Keys
When the superkey is minimal it is ____________
Primary Keys
Foreign Keys
Referencing Relation
Referenced Relation
Referential Integrity Constraint
How to use Operations and their symbols
Natural Join
Cartesian Product
Union
Intersection,
Difference
Selection
Projection
How to determine Primary keys?
A primary key must be unique and not have any duplicate entries in the attribute(s) it is designated in.
How to determine Foreign Keys?
Primary Keys of one relation are present in another relation
CHAPTER 3
Basic Types:
Char
varchar
int
smallint
numeric
real/double precision
float
CREATE TABLE
PRIMARY KEY
FOREIGN KEY
NOT NULL
INSERT INTO
DROP TABLE
alter table
Select from a single table, all/distinct, from, where and operators <, <=, >=, =, <>, and, or, not
Know how to do Cartesian Product in SQL
Know how to join (not using the JOIN syntax)
Be able to construct queries in SQL given a schema or relation
Be able to show a resulting relation give an SQL query
Know AS and be able to use it.
Know about *
Know aggregate functions