Quiz 1

Question 1

Relational Model

Answer 1

Collection of Tables to represent both data and relationships

Question 2

Tables

Answer 2

Have unique names and fixed set of columns.

AKA “Relations:

Question 3

Relation

Answer 3

In math terminology, it is a set of tuples

Question 4

Tuple

Answer 4

In laymens terms it is referred to as a row but can also be described as a sequence (or list) of values.

Question 5

Attribute

Answer 5

Column

Question 6

Domain of the attribute

Answer 6

Set of allowed values for an attribute

Question 7

Atomicity

Answer 7

indivisible

phone numbers and addresses are not atomic

Question 8

Null value

Answer 8

A special value that is unknown or does not exist.
e.g. in the instructor table, someone might not have a phone number and therefore that field will be left as null for that person.
They can cause difficulties when we access or update databases so we try to eliminate them as much as possible

Question 9

Relation Schema

Answer 9

A list of attributes and their corresponding domains.
e.g.
Instructor ( ID, name, dept_name, salary)
Department (dept_name, building, budget)

Question 10

Relation instances

Answer 10

Current values of a relation

Question 11

Does order of tuples matter?

Answer 11

No

Question 12

Should two tuples have the same value for all attributes?

Answer 12

No

Question 13

SuperKey

Answer 13

A set of one or more attributes that uniquely identify a tuple
e.g. ID or Name but ID is better because someone can have the same name

Question 14

If K is a SuperKey then:

Answer 14

Any superset of K is also a superKey. So if ID is a superkey then any combination of ID and name is also a superkey.

Question 15

Candidate Key

Answer 15

Special kind of superkey (minimal superkey). Any proper subset of candidate key can’t be a superkey.
e.g. ID and name are possible candidate keys because all other subsets of that key is not a superkey, which in this case is just zero.

Question 16

Primary Key

Answer 16

A candidate key
Chosen by DB designer
its attributes values are never, or very rarely changed.
e.g. SSN
and ID or unique identitifier might not be good when say two enterprisees merge and then id values may overlap but in our instructor table/relation that is the best we got…

Question 17

Primary Key Relation Schema

Answer 17

department(dept_name, buildings, budget)

Primary key underlined and before all other attributes

Question 18

Foreign Key

Answer 18

dept_name is a primary key for the department relation but also exists in the instructor relation so it is therefore referred to as a Foreign key. The department relation is being referenced by the instructor relation.

Question 19

Referential Integrity

Answer 19

An attribute in referencing relation should be an attribute in referenced relation.
Foreign key constraints are most common form of referential integrity.

Question 20

Relational Query languages

Answer 20

A language in which a user requests information from a database.
Higher level than standard programming language
Two types:
Procedural:user instructs system to perform a sequence of operations on DB. e.g. Relational Algebgra
Nonprocedural: User describes desired information without giving a procedure. e.g. SQL

Question 21

Relational Algebra

Answer 21

A set of operations on relations

Parallel to usual Algebra

Question 22

Usual Algebra examples

Answer 22

Operations: addition, subtraction or multiplication
Input: One or more numbers
Output: A number
e.g. z = 3x + 4y + 10

Question 23

Relational Algebra examples

Answer 23

Operations: select, project, union, set difference, cartesian product, rename, set intersection, natural join, assignment
Input: one or more relations
Output: A relation (table)
sigma_salary>=85000(instructor)

Question 24

σ (selection)

Answer 24

Return rows of the input relation that satisfy the predicate.
σ_salary>=85000(instructor)

Question 25

π (projection)

Answer 25

Output specified attributes from all rows of the input relation. Remove duplicate tuples from the output. π_ID,salary(instructor)

Question 26

BowTie (Natural Join)

Answer 26

Output pairs of rows from the two input relations (regardless of whether or not they have the same values on common attributes) Instructor BowTie deparment

Question 27

x (cartesian product)

Answer 27

Ouput all pairs or rows from the two input relations (regardless of whether or not they have the same values on common attributes) Instructor x department

Question 28

U (Union)

Answer 28

Output the union of tuples from the two input relations. | π_name(instructor)U π_name(student)

Question 29

Enitity-Relationship (ER) Model

Answer 29

E-R data model hekps to design DB schema.

Question 30

Object based data models

Answer 30

Extends E-R model with encapsulation, methods/functions, Object Identity

Question 31

Problems with Relation Model?

Answer 31

Support limited data type. - Complex domain requires complex data types (e.g. nested record structures, multivalued attributes, and inheritance) 2. Difficulty in accessing data from C++ or Java Programs

Question 32

XML

Answer 32

Extensible Markup Language | A great way to exchange data, not just documents

Question 33

Database

Answer 33

Collection of structured and interrelated data

Question 34

File Systems

Answer 34

Organizations used to store data in file systems but not anymore!

Question 35

File System drawbacks

Answer 35

Get more complex over time. e.g. adding new majors and departments Data redundancy: Can have the same data in two files Data Inconsistency: May be updated in one department but not the other Difficulty in accessing data: have to parse through everything to get the data that you want which takes time or you are always writing new programs to solve the problem Data Isolation: Data can be scattered in different files or files could be in different formats which makes retrieving the data difficult Integrity Problems: if new constraints are added or existing ones need to be changed then you might run into issues with aforementioned formatting and what not. What happens if system fails during the transaction? Chaos Concurrent-Access Anomalies Security Problems: people having access to information that they shouldn't be able to view. Hard to protect/limit in file Systems.

Question 36

DBMS

Answer 36

A collection of interrelated data (database) and a set of programs that allow users to access and modify these data. AKA Database System Examples: Oracle, Microsoft SQL server, IBM DB2, MySQL, PostgreSQL

Question 37

DBMS Top part

Answer 37

Users and interfaces | Who is who and who is allowed to do what

Question 38

DBMS Bottom Part

Answer 38

``` Disk Storage Data is stored here Indices Data Dictionary (Metadata) Statistical Data DBMS hides details of how data stored & maintained which is important for Data Abstraction ```

Question 39

DBMS Middle part

Answer 39

Query processor helps DBMS to simplify and facilitate access to data. Storage manager is important because DB typically requires a large storage space.

Question 40

Query Processor

Answer 40

Processes the Queries Query: A statement requesting information Queries are represented by a language (DB language) Two parts: - Data definition Language (DDL) - Data Manipulation Language (DML)

Question 41

Four components of Storage Manager

Answer 41

1. Buffer Manager: fetches data from disk into main memory and decides what data to cache in the main memory. 2. File manager: Manages space allocation on disk storages 3. Authorization and integrity manager: Tests for satisfaction of integrity constraints and checks authority of users access to data. 4. Transaction Manager: A unit of program that accesses and updates data items. Ensures ACID properties Transactions are initiated by SQL or programming language using ODBC/JDMC

Question 42

ACID

Answer 42

Atomicity, Consistency, Isolation, and Durability Atomicity: all or none transaction Consistency: preserves consistency of DB, e.g. Value sum of the balances of A and B must be preserved. Durability: After a successful funds transfer, new values of A and B must persist, even if system fails. Isolation: Each transaction is unaware of other transactions executing concurrently in system. For two transactions Ti and Tl, it appears to Ti that either Tj finished execution before Ti started or Tj started execution after Ti finished

Question 43

DBMS structure

Answer 43

1. More complexity 2. Data redundancy 3. Data Inconsistency 4. Difficulty in accessing data 5. Data Isolation 6. Integrity Problem 7. Atomicity Problem 8. Concurrent-Access anomalies 9. Security Problems

Question 44

Database Architecture

Answer 44

Influenced by computer system on which DB is running. Centralized vs Distributed: Database is located on single computer or data is physically divided among several several computers connected by a network