Database Management Systems Midterm Review, Exams of Data Mining

Download Database Management Systems Midterm Review and more Exams Data Mining in PDF only on Docsity!

COP-

MIDTERM REVIEW

Chapters 1 – 5, 19

M. Amanda Crick

(Uses slides from Fernando Farfan and

Eduardo J. Ruiz

Concepts:

• DBMS

Relational Model

• Levels of Abstraction

Data Independence

Chapter 1: Overview of

DBMSs

Solution  Data independence and efficient access.  Physical, logical independence  (^) Efficient storage and data retrieval  Reduced application development time.  Data storage aspect of application already written and debugged; only need to write application code  Data integrity and security.  Database prevents changes that violate integrity constraints. Views and authorization mechanism.

Solution

Data administration.  Maintenance and data administration made easier.

Concurrent access and crash recovery  Transactions prevent two conflicting operations from being carried out concurrently.  Keeps a log of changes to data, so that the system can recover from a crash.

Solution  External schemas:  (^) Allow data access to be customized at the level of individual users or groups of users using different VIEWS of the same conceptual schema.  (^) Views are not stored in DBMS but they generated on-demand.  Conceptual (logical) schemas:  (^) Describes all the data in terms of the data model. In a relational DBMS, it describes all relations stored.  (^) While there are several views for a given database, there is exactly one conceptual schema to all users.

Solution

Internal (physical) schemas:  Describes how the relations described in the conceptual schema are actually stored on disk (or other physical media).

Solution

The logical schema protects outside programs and users from changes to the database relational schema.

The physical schema protects programs and users from changes to the way database files are stored.

Concepts:

Chapter 2: Database

Design

Domain

• Attribute

Entity (Set)

• Relationship(

Set)

Primary Key

• Participation

Constraint

Aggregation

• Overlap Constraint

Descriptive Attribute

• Roles

• One-to-Many

Many-to-May

• Weak Entity Set

Identifying

Problem

A university database contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses; each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it (assuming no further constraints hold). Draw an ER diagram that captures this information.

Problem

1. Professors can teach the same course in several semesters, and each offering must be recorded. Solution Professor Teaches ssn Course Semester semesterid courseid

Problem

3. Every professor must teach some course. Solution Professor Teaches ssn Course semester courseid

Problem

4. Every professor teaches exactly one course (no more, no less). Solution Professor Teaches ssn Course semester courseid

Problem

6. Now suppose that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course. Model this situation, introducing additional entity sets and relationship sets if necessary.

Solution Professor memberof ssn Group courseid teaches Course semester gid