CSE3241 - Databases Midterm 1

What does (u) denote?

Represents a single superclass/subclass relationship with more than one superclass. ie a vehicle can belong to a registration superclass and an owner superclass

what is the definition for end user

End users are the people who want to access the database for different purposes like, querying, updating, and generating reports.

what is the definition for canned transaction

Standardized queries and updates on the database using carefully programmed and tested programs.

What are the properties of a domain?

The set of all possible values. The values are atomic with a name, data type, and format. Indivisible ie phone number cannot be split into area code and number.

What is a compound key?

a compound key is a key that consists of two or more attributes that uniquely identify an entity occurrence. ie a department has both a unique name and a unique number

ER Notation: What does a dotted line and circle denote?

a derived attribute - something that is calculated from data such as average salary

ER Notation: what does a diamond denote?

a relationship - linked to entity sets by edges

what is the definition for meta-data

description of what the data in the database means: Information about the data is called meta data. The information stored in the catalog is called meta data. The schema of a table is an example of meta data.

what is the definition for data

known facts that can be recorded and that have implicit meaning

what is the definition for database

a collection of related data

What are 9 steps of mapping an ER to relational?

1. Mapping regular entities: Create a table for each regular entity type. Create an attribute column for each simple attribute and a simple attribute for each composite attribute. ie an attribute name that branches off to first, MI, last, will become the following attributes : first, MI, last. Primary key becomes primary key. Do not create attributes for derived attributed. ------------------------------------------------ 2. Mapping weak entities: Create a table for the weak entity that includes all the attributes. Add an attribute that contains the primary key for the parent of the weak entity. ie if dependents is a weak entity of employees you would add an attribute for employee # in the weak entity table, this is the foreign key. ------------------------------------------------ 3. Mapping of Binary 1:N: Introduce a foreign key in the relation on the "many" side that references the table on the "one" side. Do not need to introduce a separate table. ------------------------------------------------ 4. Mapping of Binary 1:1 Relationship Types: Cross reference - create a new table and include the primary key of each relationship. ie each department has 1 manager: Dept_Manager(dept_no, employee_no) ------------------------------------------------ 5. Mapping of Binary M:N Relationship Types: Same as cross reference: create a new table and include the primary key of each relationship. ie many employees work on projects and projects have many employees Proj_Work(Proj_no, Emp_no) where Proj_no and Emp_no are both primary keys ------------------------------------------------ 6. Mapping of Multivalued Attributes: Create new table that contains attributes. Include a foreign key that links to the primary key of the entity. ------------------------------------------------ 7. Mapping of N-ary Relationship Types: Create a new table to represent the relationship. Include foreign keys to all the primary keys of the relationship participants and any attributes that belong to the relationship. ie SPJ (S#, P#, J#, QTY) Foreign key S# References Supplier Foreign key P# References Part Foreign key J# References Project ------------------------------------------------ 8. Mapping of specialization: Create new table for each specialization. ie employee has secretary, technician, engineer types. Create a table for each one and add a foreign key to primary key of employees. secretary(ssn, typing_speed) technician(ssn, tgrade) etc 9. Mapping of Union Types (Categories): Defining superclasses have different keys. Specify a new key attribute: a surrogate key. See ER_Relational II slide 24

what is the definition for deductive database system

A Deductive database is a database system that can make deductions (i.e., conclude additional facts) based on rules and facts stored in the (deductive) database

what is the definition for database catalog

A database catalog contains complete description of the databases, database objects, database structure, details of users, and constraints etc. that are stored.

what is the definition for DBMS

A database management system (DBMS) is a collection of programs that enables users to create and maintain a database.

what is the definition for database system

A database system comprises a database of operational data, together with the processing functionality required to access and manage that data. The combination of the DBMS and the database is called database systems

What is an Entity Set?

A group of similar entities e.g. all persons, all automobiles, all employees

What are the properties of superclasses and subclasses?

A member entity of the subclass represents the same real-world entity as some member of the superclass. --------------------------------------------------------- The subclass member is the same as the entity in the superclass, but in a distinct specific role. --------------------------------------------------------- When implementing a superclass/subclass relationship, a member of the subclass may be represented as a distinct database object a distinct record that is related via the key attribute to its superclass entity. --------------------------------------------------------- An entity in a subclass must also be a member of the superclass. --------------------------------------------------------- It is not necessary that every entity in a superclass be a member of some subclass Example: A technical writer is an employee but does not belong to any subclasses. --------------------------------------------------------- An entity can be a member of more than one subclass. Example: A salaried employee who is also an engineer belongs to the two subclasses ENGINEER and SALARIED_EMPLOYEE

Whats the difference between a simple domain and a composite domain?

A simple domain is atomic. A composite domain is a product of simple domains such as Month x Day x Year where Month = {1, 2, ..., 12} Day = {1, 2, ... 31} Year = { 1900, 1901, ...}

What is a superkey?

A subset of the relation attributes where all tuple values must be distinct. -------------------------------------------------------------- ie EMP( EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION) Superkeys: EMPNO, NAME, DNO, JOB, MGR, SAL, COMMISSION------------ EMPNO, NAME, DNO, JOB------------- EMPNO, NAME-------------- EMPNO, NAME, SAL, COMMISSION---------------- EMPNO

What is an Entity?

A thing that exists e.g. person, automobile, department, employee

what is the definition for transaction-processing application

A transaction is a logical unit of database. The processing includes one or more database operations like, insertion, deletion, modification and retrieval. The database operations that form a transaction can either be embedded within an application program on they can be specified interactively via a high-level query language such as SQL.

What is a compound or composite attribute?

An attribute with children attributes. For example an attribute name could have first_name and last_name attributes

What are some things to remember about keys and superkeys?

Any Key is a SuperKey (but not vice versa)------------ Any set of attributes that includes a key is a SuperKey------------ A minimal SuperKey is also a Key

What is ACID?

Atomicity Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen. -------------- Consistency The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors cannot result in the violation of any defined rules. -------------- Isolation The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction. -------------- Durability Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

What does the completeness constraint denote?

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. Represented by a double line connecting the superclass to the circle.

ER to relational: How do you map N-ary relationships?

Create a new table to represent the relationship. Include foreign keys to all the primary keys of the relationship participants and any attributes that belong to the relationship. ie SPJ (S#, P#, J#, QTY) Foreign key S# References Supplier Foreign key P# References Part Foreign key J# References Project

ER to relational: How do you map a regular entity?

Create a table for each regular entity type. Create an attribute column for each simple attribute and a simple attribute for each composite attribute. ie an attribute name that branches off to first, MI, last, will become the following attributes : first, MI, last. Primary key becomes primary key. Do not create attributes for derived attributed.

ER to relational: How do you map a weak entity?

Create a table for the weak entity that includes all the attributes. Add an attribute that contains the primary key for the parent of the weak entity. ie if dependents is a weak entity of employees you would add an attribute for employee # in the weak entity table, this is the foreign key.

ER to relational: How do you map specializations?

Create new table for each specialization. ie employee has secretary, technician, engineer types. Create a table for each one and add a foreign key to primary key of employees. secretary(ssn, typing_speed) technician(ssn, tgrade) etc

ER to relational: How do you map multivalued attributes?

Create new table that contains attributes. Include a foreign key that links to the primary key of the entity

ER to relational: How do you map binary 1:1?

Cross reference - create a new table and include the primary key of each relationship. ie each department has 1 manager: Dept_Manager(dept_no, employee_no)

What are the 4 types of DBMS languages?

Data definition language (DDL) Defines internal and conceptual schemas---- Storage definition language (SDL) Specifies the internal schema---- View definition language (VDL) Specifies user views/mappings to conceptual schema---- Data manipulation language (DML) Allows retrieval, insertion, deletion, modification

What is a schema construct?

Each object in the schema

what is the definition for DBA

Database Administrator: >DBA is a person who is responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed.

What four main types of actions involve databases? Briefly discuss each.

Defining a database involves specifying the data types, structures, and constraints for the data to be stored in the database. ---------------------------------------------------------------- Constructing the database is the process of storing the data itself on some storage medium that is controlled by the DBMS. ---------------------------------------------------------------- Manipulating a database includes such functions as querying the database to retrieve specific data, updating the database to reflect changes in the miniworld, and generating reports from the data. ---------------------------------------------------------------- Sharing a database allows multiple users and programs to access the database concurrently. 1.3. Discuss the main characteristics of the database approach and how it differs from traditional file systems.

ER to relational: How do you map union types?

Defining superclasses have different keys. Specify a new key attribute: a surrogate key. See ER_Relational II slide 24

What is a database schema?

Description of a database

What are the 4 possible constraints on specializations?

Disjoint, total (one and only one) (d) with double line --------------------------------------------------------- Disjoint, partial (zero to one) (d) with single line --------------------------------------------------------- Overlapping, total (one to many) (o) with double line --------------------------------------------------------- Overlapping, partial (zero to many) (0) with single line

What is a schema diagram?

Displays selected aspects of schema

What is a weak entity and how is it denoted?

Do not have key attributes of their own, Identified by being related to specific entities from another entity type, Always has a total participation constraint. ie a dependent of an employee. Denoted by a double rectangle.

What is three schema architecture and what does each level represent?

External or view level Describes part of the database that a particular user group is interested in User views Describes parts of database a user is interested in Hides the rest Different users, different views -------------------------------------------------- Conceptual level Describes structure of the whole database for a community of users Hides details of physical storage Describes entities, relationships, constraints, etc. Usually the level where the database is actually designed and implemented -------------------------------------------------- Internal level Describes physical storage structure of the database

What are the different data abstraction models?

High-level model (conceptual model) How do users perceive the data? Entities real-world concepts or objects (Student) Attributes some property of an entity (GPA) Relationship - association between two entities "Students" are enrolled in "sections -------------------------------------------------- Low-level model (physical data model) How is the data actually stored in the computer? -------------------------------------------------- Representational model (implementation model) Midway between high-level and low-level Still a way to organize the data so that it can be understood by users But also still related to how the data is physically stored -------------------------------------------------- Relational data model A widely-used representational data model Will be the focus of this course

what is the definition for program-data independence

In traditional file processing, the structure of the data files is 'hard-coded" into the programs. To change the structure of the data file, one or more programs that access that file, should be changed. The process of changing can introduce errors. In contrast to this more traditional approach, DBMS access stores the structure in a catalog, separating the DBMS programs and the data definition. Storing the data and programs separately is known as program-data independence.

ER to relational: How do you map binary 1:N?

Introduce a foreign key in the relation on the "many" side that references the table on the "one" side. Do not need to introduce a separate table.

what is the definition for persistent object

Object-Oriented database systems are compatible with programming languages such as c++ and JAVA. An object that is stored in such a way that it survives that termination of the DBMS program is persistent.

What does the overlap constraint (o) denote?

Overlap(o) specifies that the subclasses are not constrained to be disjoint the same (real-world) entity may be a member of more than one subclass of the specialization. Overlap is the default constraint and displayed by placing an o in the circle.

What is an Attribute?

Property of an entity or relationship e.g. person - name, address

ER to relational: How do you map N:M?

Same as cross reference: create a new table and include the primary key of each relationship. ie many employees work on projects and projects have many employees Proj_Work(Proj_no, Emp_no) where Proj_no and Emp_no are both primary keys

Discuss the 4 main characteristics of the database approach and how it differs from traditional file systems.

Self-describing nature of a database system ---------------------------------------------------------------- Insulation between programs and data, and data abstraction ---------------------------------------------------------------- Support of multiple views of the data ---------------------------------------------------------------- Sharing of data and multiuser transaction processing ---------------------------------------------------------------- With traditional file systems each user defines and implements the files needed for a specific software application but with database systems a single repository maintains data that is defined once and then accessed by various users

What is a Domain?

Set of values allowed for an attribute

What is a view of data?

Subset of the database - Contains virtual data derived from the database files but is not explicitly stored

What are the responsibilities of the DBA and the database designers?

The DBA is responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed. The DBA is accountable for problems such as security breaches and poor system response time ---------------------------------------------------------------- Database designers are responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data. These tasks are mostly undertaken before the database is actually implemented and populated with data. It is the responsibility of database designers to communicate with all prospective database users in order to understand their requirements and to create a design that meets these requirements.

What is an identifying relationship?

The technical definition of an identifying relationship is that a child's foreign key is part of its primary key. CREATE TABLE AuthoredBook ( author_id INT NOT NULL, book_id INT NOT NULL, PRIMARY KEY (author_id, book_id), FOREIGN KEY (author_id) REFERENCES Authors(author_id), FOREIGN KEY (book_id) REFERENCES Books(book_id) ); book_id is a foreign key, but it's also one of the columns in the primary key. So this table has an identifying relationship with the referenced table Books. Likewise it has an identifying relationship with Authors.

what is the definition for user view

The way in which the database appears to a particular user is called user view.

ER Notation: what does a circle denote?

an attribute - linked to entity sets or relationships by edges

ER Notation: what does a rectangle denote?

an entity set

ER Notation: What does a double diamond denote?

an identifying relationship

Suppose a relation, EMPLOYEE(EMPNO(KEY), NAME, DNO, MGR, SAL) which of these would violate the Entity integrity constraint? a) a null value in the NAME attribute b) two tuples with the same value in the NAME attribute c) a null value in the EMPNO attribute d) duplicate values in the EMPNO attribute

c

Discuss the capabilities that should be provided by a DBMS.

control redundancy, control access, Efficient query and update processing, backups, integrity constraints, Flexibility, Instant updates to information

When would you not use a DBMS?

cost limitations, single user, limited data manipulation/cross-referencing, requirements not expected to change at all, limited space

A three schema DB architecture consists of: a) database, application, and client schemata b) physical, logical, and export schemata c) server, client, and communication schemata d) internal, conceptual and external schemata

d

Discuss the differences between database systems and information retrieval systems.

database systems work with relational databases(well structured data) while information retrieval system works on unstructured data (raw text files/documents)

What are the following relationship cardinalitys http://jcsites.juniata.edu/faculty/rhodes/dbms/images/card1.gif

each class can be assigned to only 1 faculty member (many to 1), there is exactly 1 chairperson per department (1 to 1), students can enroll in many classes (many to many)

ER Notation: How do you denote a total relationship? ie Every employee must work for a department

http://www.kkhsou.in/main/EVidya2/computer_science/comscience/401.gif

ER Notation: what does a double circle denote?

multi-valued attribute e.g. a student can have more than 1 major

What does the disjoint constraint (d) denote?

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 In EER diagram, d in the circle stands for disjoint.

ER Notation: How do you denote a unique attribute or key?

underline the attribute name

ER Notation: How do you denote a partial key?

with a dashed underline

What are the different types of database end users? Discuss the main activities of each.

■ Casual end users occasionally access the database, but they may need different information each time. They use a sophisticated database query language to specify their requests and are typically middle- or high-level managers or other occasional browsers. --------------------------------------------------------- ■ Naive or parametric end users make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database, using standard types of queries and updates—called canned transactions—that have been carefully programmed and tested. --------------------------------------------------------- ■ Sophisticated end users include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the facilities of the DBMS in order to implement their own applications to meet their complex requirements. --------------------------------------------------------- ■ Standalone users maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces.An example is the user of a tax package that stores a variety of personal financial data for tax purposes.