CPS 510 - Database Systems

Examples of database users (Topic 1)

-Application programmers

-End users

-Database administrators (IT professionals)

DBA vs DA (Topic 1, Users)

DBA (Database administrator)

-Their job is to create the database and put in technical controls to enforce policy decisions

DA (Data Administrator)

-Job is to decide what data should be stored and who can perform operations on data

What is persistent data? What was the early name for persistent data? (Topic 1)

Data that is in the database for a long period of time.

The earlier term used to be named operational data.

What is a database? (Chapter 1)

A collection of related data.

What is a DBMS? (Chapter 1)

A Database Management System is a computerized system that enables users to create and maintain a database.

What is database manipulation? (Chapter 1)

Includes functions such as:

-querying the database to retrieve data

-updating the database

-generating reports from the data

What is a query? (Chapter 1)

A cause for some data to be retrieved

Pros of using a database/why use a database? (Topic 1)

-Shared data

-Reduced redundancy of data

-Reduced inconsistent data

-Transaction support (Making several update operations at once)

-Support for data integrity (accuracy of data)

-Security enforcement (restriction of data to authorized personnel only)

-Support for standards (Ex. Phone number must be 9 digits or longer)

True/False (Topic 1):

In database systems the logical and physical representation of data are separated

True: In database systems the logical and physical representation of data are separated

What is a relation? (Topic 1)

A mathematical term for TABLE

What is meta-data? (Chapter 1)

It describes the structure of the primary database.

A database system contains not only the database itself but also a complete definition or description of the database structure and constraints.

True/False (Chapter 1):

All database systems require metadata.

False: Some newer types of database systems do NOT require metadata.

Instead, the data is stored as self-describing data.

What is a 'view' in a database? (Chapter 1)

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

Data can be stored or derived. In this case, data would be derived from the table into a view.

What is concurrency control software? (Chapter 1)

Software that ensures that several users can update the same data in a controlled manner so that the result of the updates is correct.

What is a DBA? (Chapter 1)

The Database Administrator is responsible for:

-authorizing access to the database

-coordinating/monitoring its use

-acquiring software and hardware resources as needed

-accountable for security breaches

What is a database designer? (Chapter 1)

Responsible for:

-identifying the data to be stored in the database

-choosing appropriate structures to represent and store this data

-develop views for the database

What is an end user? What are the types of end users? (Chapter 1)

People whose jobs require access to the database for querying. Types of end users are:

-casual end users (users who occasionally access the database)

-naive/parametric end users (users who query/update the database)

sophisticated end users (implement their own applications on a DBMS)

-standalone users (maintain their own personal databases)

What is a database schema? (Topic 2)

The description of the database which is specified during the design phase. (It does not change frequently)

What is data normalization used for? (Chapter 1)

To minimize the duplication of information or protect the database against logical/structural data abnormalities.

What is denormalization? (Chapter 1)

Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.

When is the instance of a database changed? (Topic 2)

Every time we insert, delete, update/modify the value of a data item, we change one instance of database to another.

What is Data independence? What are the two types of data independence? (Topic 2)

The capacity to change the schema at one level without having to change the schema at the next higher level.

- Logical data independence

- Physical data independence

When do we need to make changes to conceptual/logical schema? (Topic 2)

When the logical structure of the database changes.

When do we need to make changes to physical schema? (Topic 2)

To improve performance of data retrieval or data update (Ex. Adding a new structure)

True/False, why? (Topic 2)

Achieving logical data independence is just as difficult as physical data independence.

False: Achieving logical data independence is MORE difficult than physical data independence.

This is because application programs heavily rely on the logical structure of the data they access.

What is DDL in database languages? (Topic 2)

Data Definition Language is used to define the database and defines the schemas at various levels.

What is DML in database languages? (Topic 2)

Data Manipulation Language is used to manipulate data and is used to construct and use the database. (ex. insert, update/modify and delete statements)

What is a data dictionary? (Topic 2)

Contains the metadata of a system. Is meant to contain 'data about the data'. Can also contain an explanation of the relationships in a database.

What is a data model?

A collection of concepts that can be used to describe the structure of a database

What is the difference between a High-level/conceptual data model vs a low-level/physical data model? (Chapter 2)

High-level/conceptual data models provide concepts that are close to the way many users perceive data, whereas low-level or physical data models provide concepts that describe the details of how data is stored on the computer storage media.

What is an entity? (Chapter 2)

Represents a real-world object or concept, such as an employee that is described in the database. (Identified as a rectangle in ER diagram)

What is an attribute? (Chapter 2)

Represents some property of interest that further describes an entity, such as the employee's name or salary. (Identified as an oval in ER diagram)

What does a relationship between entities mean? (Chapter 2)

A relationship among two or more entities represents an association among the entities (ex. a works-on relationship between an employee and a project)

What is a entity-relationship model? (Chapter 2)

A high-level conceptual data model for databases

What are the three parts of the Three Schema Architecture? (Chapter 2)

1. Internal level which has an internal schema (describes physical storage/structure of the database)

2. Conceptual level which has a conceptual schema (describes the structure of the database for users)

3. External/view level which has an external schema/user views. (describes only part of the database that the user is interested in and hides the rest)

What is logical data independence? (Chapter 2)

The capacity to change the conceptual schema without having to change external schemas or application programs (adding record types or data items).

What is physical data independence? (Chapter 2)

The capacity to change the internal schema without having to change the conceptual schema or external schema.

What does a query optimizer do in a DBMS? (Chapter 2)

Concerned with the rearrangement and possible reordering of operations, elimination of redundancies, and use of efficient search algorithms during execution.

What is three-tier architecture? (Chapter 2)

A database architecture which adds an intermediate layer between the client and the database server (ex. application programs, web pages)

What is client-server architecture? (Chapter 2)

An underlying framework that consists of many PCs/workstations and mobile devices as well as a smaller number of server machines, connected via wireless networks or LANs and other types of computer networks.

What does an ER (entity-relationship) model consist of? (Topic 3)

Entities, Relationships and Attributes

What is the difference between strong entities vs weak entities? (Topic 3)

Strong entities are entities that can exist on their own.

Weak entities are entities that depend on the existence of another strong entity. (Indicated by a double rectangle)

What does the 'degree' of a relationship mean? (Topic 3)

It is the number of participating entity types in the ER diagram.

What are the 3 types of 'relationships' in an ER diagram? What do they mean? (Topic 3)

-one-to-one (1:1)

(ex. 1 manager per 1 department)

-one-to-many (1:N OR 1:M)

(ex. 1 department can have many employees)

-many-to-many (M:N)

(ex. a supplier can supply many projects, and a project can receive parts from many suppliers)

What are the 2 types of 'participation' in an ER diagram? What do they mean? (Topic 3)

-Total participation (indicated by double lines)

(ex. every department MUST be managed by a manager)

-Partial participation (indicated by single lines)

(ex. not every department is managed by a manager)

True/False (Topic 3):

An attribute can have a NULL value.

True: An attribute can have a NULL value

What is the difference between simple vs composite attributes? (Topic 3)

Simple attributes are not divisible by parts.

Composite attributes consist of several simple attributes.

What is the difference between 'single-valued' vs. 'multi-valued' attributes? (Topic 3)

Single valued: A single value for a particular entity (ex. a car can only be one colour at a time)

Multi-valued: Can have multiple values for a particular entity (ex. list of degrees, English degree, Comp sci degree at the same time). Indicated by a double circle in the ER diagram.

What is the difference between a 'stored' vs. 'derived' attribute? (Topic 3)

A stored attribute was entered as data (ex. date of birth)

A derived attribute is be derived from previous data that was already entered in the database (ex. 'age' can be found from date of birth with calculations). They indicated by a dashed circle in the ER diagram.

What is a key attribute? (Topic 3)

An attribute that is unique and distinct for each individual entity instance.

Key attributes are underlined in the ER diagram.

True/False (Topic 3):

Primary key of a weak entity type is formed by the primary key of the associated strong entity plus the weak entity discriminator.

True: Primary key of a weak entity type is formed by the primary key of the associated strong entity plus the weak entity discriminator.

What is attribute inheritance? (Topic 3)

When the member of a subclass inherits all the attribute of its superclass.

Each subclass can have its own attributes, in addition to the inherited attributes.

What is the subclass of an entity? (Topic 3)

The sub-grouping of occurrences of entities in an entity. The subclass inherits the characteristics of a superclass.

What is the superclass of an entity? (Topic 3)

It is the class from which many subclasses can be created in the entity.

What is specialization of an entity? What are the 4 types of specialization? (Topic 3)

The process of defining a set of subclasses of an entity type. The entity type is called the superclass of the specialization.

- disjoint, total

- disjoint, partial

- overlapping, total

- overlapping, partial

What is the difference between 'disjoint' vs 'overlapping' constraint in specialization? (Topic 3)

Disjoint: An entity can be a member of at most one of the subclasses of the specialization (Identified by d in ER diagram)

Overlapping: The same entity can be a member of more than one subclass of the specialization (Identified by o in ER diagram)

What is the difference between total and partial constraint in specialization? (Topic 3)

Total: Every entity in the superclass must be a member of some subclass in the specialization (Indicated by double line)

Partial: An entity may not belong to any of the subclasses in the specialization (Indicated by single line)

What does generalization mean? (Topic 3)

Result of taking the union of two or more lower-level entity types to produce a higher-level entity type.

It is the inverse of specialization.

What is aggregation? (Topic 3)

An abstraction through which relationships are treated as higher-level entities.

What is the number of attributes (columns) in a table called? (Topic 4)

Degree or arity

In a table, rows are called __________ and columns are called _________ . (Topic 4)

rows are called 'tuples' and columns are called 'attributes' .

What is the name of the data type that describes the values that can appear in a column? (Topic 4)

Domain

A domain is a set of atomic values, and each value is indivisible.

What is the optimizer? (Topic 4)

It is a system component that determines how to implement user requests.

What is the catalog? (Topic 4)

A set of system relvars whose purpose is to contain descriptors regarding the various objects that are of interest to the system itself.

What is a transaction? (Chapter 5)

An executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database.

Begins by BEGIN TRANSACTION and terminates normally or abnormally.

What is Atomicity in transactions? (Topic 4)

Means that transactions are guaranteed either to execute in their entirety or not to execute at all, even if the system fails halfway through the process.

What is Durability in transactions? (Topic 4)

Means that once a transaction successfully commits, its updates are guaranteed to be applied to the database, even if the system subsequently fails at any point.

(ex. if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes)

What is Isolation in transactions? (Topic 4)

Means that database updates made by a given transaction T1 are kept hidden from all distinct transactionsT2 until and unless T1 successfully commits.

What is Serializability in transactions? (Topic 4)

The interleaved execution of a set of concurrent transactions is guaranteed to produce the same result as executing those same transactions one at a time in some (unspecified) serial order

What is a Commit (normal termination) in transactions? (Topic 4)

The operation that signals successful end-of-transaction.

Any updates made to the database are now "made permanent" and become visible to other transactions.

What is a rollback (abnormal termination) in transactions? (Topic 4)

The operation that signals unsuccessful end-of-transaction.

Any updates made to the database are "rolled back" (undone) and are never made visible to other transactions.

What does the 'entity integrity constraint' state? (Chapter 5)

That no primary key value can be NULL.

This is because having NULL values for the primary key implies that we cannot identify some tuples.

What is the precedence of logical operators in SQL? (Topic 5-6-7)

NOT highest

AND middle

OR lowest

what is data integration

combining distinct files into one unified database, reducing redundancy

what was the most important innovation in database history in 1969

the relation model, where data could be represented as tables based on logic and mathematics

what does data independence mean for the applications

it allows for applications to change without needing to alter the underlying database structure

difference between schema and instance

instance is the database with all of the entities and data values. schemas are only the description of the entities being used in the database

three levels of architecture

used to describe the structure of three schema architecture. the levels are internal, conceptual and external levels

internal level of architecture

closest level to the physical storage. it shows how the data is stored inside the system. this level deals with file organizations and access paths.

conceptual level

deals with the modeling of the whole database. the schema of the database is defined at this level. it is essentially the entities that will be seen by the users

external level

this levels models a user oriented database. it is the view that the users see

logical data independence

able to modify conceptual schema without changing. (able to change external views and application programs)

physical data independence

ability to modify internal or physical schema without changing. (conceptual or view level schema). achieving logical data independence is harder then physical data independence

DBMS languages

DDL (defines the database) DBA and database designers are the users

DML (manipulates the data) end users(external users)

three steps of conceptual schema

1. choice of model

2. normalization

3. optimization

choice of model

user requirements and real world relations should be the result of this stage

normalization

adjusted diagrams and a normalized relational model

optimization

result of this stage is data dictionary and database description

ER model

consists of entities, relationships and attributes

entities

thing that exist in the real world with an independent existence

strong entity types

called owner or dominant entity types. these exist on their own. (ie. it does not need a weak entity to exist)

weak entity types

also called dependent or subordinate entities. existence of weak entity depends on the existence of a strong entity. (ie. if there is no strong entity, there cannot be a weak entity)

relationships

associates one entity with another. degree of a relationship is the number of participating entities

mapping constraints

they can be one to one, one to many, many to many

total participation (one of two participation constraints)

every entity in the set must participate in one relationship

partial participation

only some entities of the set participate in the relationship

recursive relationship

each entity in the set in the relationship plays a role

single versus multi valued attributes

single valued would be something you only have one of. something like SIN. multi valued is something that you can have multiple of. something like what skills you have

stored versus derived attributes

stored would be something like date of birth, where derived is something like age

key attributes

an attribute that is unique. it is distinct for each entitiy. they are underlined in ER diagrams.