COP4710 Exam 1
Name the decade in which these technologies were introduced Magnetic Tape: Magnetic Disk: Floppy Disk: Optical Disk:
50's 60's 70's 80's
KSDS is essentially a _________ and can be ___________ length
B-tree, variable length
Describe a unary relationship where one EMPLOYEE manages (zero or many) EMPLOYEES. Think of the E/R diagram picture. The correct picture is on the flip-side of this notecard
"one EMPLOYEE can manage (zero or many) EMPLOYEES'
load factor = ___________ / ______________ (in database file storage)
# of records in file / max # of records file can contain
Explain direct and indirect data
(verify in from notes) direct-in database indirect-data that is not in database, but is materilzed
List the functions of database
-Data Definition Language: DDL -Data Manipulation Language: DML e.g. "select" -Data Security and Integrity: e.g. accounts, privileges etc. -Data Recovery & Concurrency: -Data Dictionary: all the various schemata, mappings) -Performance
Name and describe the three types of entities
-Regular entities: have independent existence -Weak entities: cannot exist exist except with identifying relationships with an owner(regular) entity type Associate entities: formed from many-to-many relationships between other entity types. these may be gerunds.
Give several examples of Relationship Types.
-Unary -Binary -Ternary
What are the two types of DMLs? Briefly explain them
-planned requests: foreseen in advance -unplanned: unforeseen, query on demand (ah-hoc)
DBMS needs to protect an application from
-representation of numerical data(packed decimal, character string etc. ) -representation of character data (ASCII, EBCDIC) -units for numeric data (inch, units etc) -data coding & encoding (look at notes - storing color as number0 -data materialization (construction of logical field, direct and indirect -structure of stored data -structure of files
Advantage and Benefits of DB approach
-sharing -redundancy -inconsistency mitigated -transaction supported provided -integrity can be maintained -security restrictions can be applied -conflicts can be balanced -standards can be applied
What are the 3 level data base management system architecture
1. Internal 2. Conceptual 3. External (user-level)
name the VSAM data sets
ESDS entry sequenced data set RRDS relative record data set KSDS key sequenced data set
Explain the Network model
Each record can have multiple parents -perceived by user as collection of tables
True or False: The external view of the database architecture consists only of end users
FALSE It can also be an application programmer and DBA DBA, however, is sort of a special case since he/she is concerned with internal, conceptual, and external levels
True or False: The internal level of database architecture concerns itself with the details of blocks and pages
FALSE The internal view is still on removed from the physical level since it doesn't deal with the actual physical records-also called blocks or pages Its view assumed an unbounded linear address space
True or False: A composite attribute cannot be split into smaller subparts which represent more basic attribute
FALSE This is defined as a simple attribute (a.k.a. atomic attribute)
True or False: The External Level is a single user view.
False: The External Level is usually made up of several views each consisting of some portion of total database. However, it is the user view.
In division hashing (modulo hashing), sometimes you see +1 in the formula. Why is this?
Historically, some software didn't support access at index 0. This was to avoid this. recall: page 0 is used for special purpose
In dynamic hashing, what happens if a bucket overflows?
In this case, 52 was added. Prior to that, the depth was 2
What was the first relational database?
Ingress
What is a "weak identity" and how is it shown on an E/R diagram?
It is shown with a double lined rectangle (add more detail)
Name the types of sequential files
MASTER FILE: original file TRANSACTION FILE: any kind of changes to record REPORT FILE: what your program generates WORK FILE: intermediate file (tmp for sort) PROGRAM FILE: code you put into memory TEXT FILE: documentation file
Name the 5 types of file organization
SEQUENTIAL FILE RELATIVE FILE INDEXED FILE INDEXED SEQUENTIAL ACCESS METHOD (ISAM) VIRTUAL STORAGE ACCESS METHOD (VSAM)
Why use datase?
SINGLE USER -compactness -speed -less handwork (manual vs. mechanic) -currency -protection MULTIUSER -centralized control
Explain briefly the role of the DA
Senior manager, decides how the data will be stored (check your reading notes)
Name the various types of file hashing
Static external hashing Dynamic Extendable hashing
Show and describe hierarchic compression
Stored record, consist of two parts: A fixed part (city field) A varying part (set of supplier) [ repeating group ]
SQL stands for
Structured Query Language
True or False: A composite attribute can be a key attribue
TRUE
True or False: Weak entities always have a total participating constraint because they cannot be identified without an owner entity
TRUE
Explain the "Bill of rights relationship" N:N Unary relationship and give an example.
The Bill of Material is an N:N unary relationship where each item is produced by other items. e.g. An automobile may be produced by combining Engine, Chassis, and Body items
What is an Identifying Relationship?
The relationship associating the weak entity with the identifying relationship. The participation of the weak entity set in the relationship is total
What are mappings and what are their functions with regards to the ANSI/SPARC 3 level architecture
They define the correspondence between the conceptual/internal and external/conceptual.
True or False: If the composite attribute is referenced only as a whole then there is no need to respresent it in the component attribute
True
True or False: Total participation in the identifying relationship always exists
True
True or False: A weak entity can be identified uniquely only by considering the primary key of another (owner) entity
True The primary key of a weak entity set is a combination of partial key and primary key of the strong entity set.
Name the major components of the database system
USER SOFTWARE HARDWARE DATA
Dynamic and Extendible use the ___________ representation of the hash value h(K) in order to access the directory
binary
In dynamic hashing the directory is a ___________ ___________
binary tree
A group of records with one address is called a _______________
bucket
The control area can also be thought of as a __________________
bucket
a VSAM data set is
collection of control intervals collection of one or more control areas
DBMS regards disk as
collection of files
file
collection of records
Define Relationship type and how it is shown visually (shape) on an E/R diagram
collection of relations of the same type associating entities of the same type. -shown as horizontal diamond shape
Define Entity Type and how it is shown visually (shape) in an E/R diagram
collections of entities or objects of the same type. -shown as rectangle in E/R diagram
Cloud computing is essentially __________________ computing
distributed
What is the most widely used method for hashing?
division hashing
How do you represent multiple values (multiple cols) in the E/R diagram?
double ellipse
Dynamic and Extendible hashing allow what?
dynamic growth and shrinking of number of files in records
What are the advantages of VSAM file system
dynamic insertion of records by reorganization of space through movement of data records rather than overflow areas, VSAM distributes free space though out control intervals and control areas at data set creation
What is meant by ad-hoc language?
essentially, it is an unplanned DML request. a.k.a. query on demand
The ANSI/SPARC term for an individual user's view on the external level is an ______________ ________________
external view
What is meant by Relative File file system?
file system in which records are accessed by reference to their relative position (r.p) in the file
define record
group of fields (addressable units)
___________________ popularized indexing
hard drives
What is the Data Dictionary?
information about database; all various, schemata, mappings
What is existence dependency?
instance of one entity that cannot exist without the existence of some other related entity
Why is SQL considered non-procedural language?
it does not loop
What is the internal level and what is another name for it?
level closes to physical storage; concerned with the was data is stored inside the system. a.k.a. "storage level"
A bucket is similar to the data structure ___________ ___________
linked list
Data models are either__________ or ____________
logical, physical
Explain the hierarchical structure
logically represented by tree -each parent can have multiple children -each child has only one parent -root segment -lvl 1 -lvl 2 -etc.
What is the function of the optimizer
looks at how to respond to user request to provide the most efficient manner to do so
What is Directory Lookup?
method for faster retrieval. create dictionary based on primary key so as to quicken lookup without losing the relative position. Allows sorting based on primary key. The dictionary will have a pointer to the relative file position. *no limit to how many you can have
What is clustering and what are the different types?
method of storing records that are logically related (frequently used together) physically close together; less physical I/Os. intrafile clustering: file clustering in single file interfile clustering: clustering across several files
What is the issue having two logically related records on two different pages
more I/Os
In extendible hashing, the directory is an array . . .
of size 2^d where d is called the global depth
What was the problem with ISAM?
over flow; must keep sequential
define Domain in terms of database
pool of values
Unary is also known as a _____________ relationship
recursive
Give a brief description of DBA's role
responsible for central control of system at technical level at the direction of DA
What is a partial key?
set of attributes than can uniquely identify weak entities that are related to some owner entity (a.k.a. discriminator)
DBMS requests _________File manager requests________Disk Manager performs ________________
stored record, stored page, disk I/O
Describe a relationship where EMPLOYEE manages DEPARTMENT, where the relationship would be one-to-(zero or one) respectively Think of the E/R diagram picture. The correct picture is on the flip-side of this notecard
the circle in the diagram means "0" or "1" occurrences
What is a composite attribute, and why would it be of use?
useful to model situations in which a user refers to the composite attribute as a unite but other times refer specifically to its components
Briefly explain the Disk Manager
views data as a collection of stored files e.g. index files, hash files, VSAM, ISAM (double check from notes/book)
An entity than cannot be uniquely identified by its attributes alone and must use a foreign key in conjunction with its attribute to create a primary key is a ____________ ___________
weak entity
What is a control area split?
when a control area runs out of space for a control interval split, VSAM then establishes new control area and performs a control area split by moving approximately half of the control intervals of the full control area and by modifying the indexes to reflect those changed locations
What is a control interval split?
when control interval runs out of free space, VSAM has the control interval divide into two control intervals
How are weak entities relationships visually shown on an E/R diagram?
with a double lined diamond, as shown
Who created the entity model and when?
Peter Chen 1976
What is the difference between Relative addressing and Absolute addressing in terms of database file storage?
ABSOLUTE ADDRESSING: record's actual address on secondary storage (internal tree structure) RELATIVE ADDRESSING: record's ordinal number in file (i.e. similar to array software addressing)
What group created the 3 level database architecture model?
ANSI/SPARC
