MASTER SET OF EXAM 2 for Data Modeling

How to know when to use and not to use

When to use: - app involves GUI, implemented across platforms, multiple components to work together, a lot of string manipulation, change rapidly When not to use: - has complex algorithm, large data sets, app is established functions that will not be changing over time

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

Relationship set

collection of similar relationships

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

WHERE

condition for information in relation(s)

Data control language(DCL)

control rights and permissions of data access in database

CREATE TABLE

creates relation

Entity Type

defines a collection (or set) of entities that have the same attributes. Represented as a rectangular box enclosing the entity type name. Describes the schema or intension for a set of entities.

Data definition language (DDL)

defines and changes the structure of database. (create,drop,alter)

Which SQL statement is used to delete data from a database?

delete

DROP TABLE

destroys the relation as well as the schema information and tuples

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

Distinct

eliminates duplicate rows from query

<>

equal to

What is meant by ad-hoc language?

essentially, it is an unplanned DML request. a.k.a. query on demand

ISA

every A entity is also considered to be a B entity

thick arrow

every entity of this entity set must appear at least once in the other entity set

A correlated subquery is executed each time

every time the outer query reads a row

The ANSI/SPARC term for an individual user's view on the external level is an ______________ ________________

external view

A mandatory participation constraint always indicates that an entity is weak.

false

An advantage in expressing a data model as an ER diagram is that ER diagrams are very uniform in their notation and all database designers use identical standards in producing ER diagrams.

false

Is a foreign key always the key of some other table?

false

Primary keys are part of Chen's entity relationship model

false

Rows in a relational table need not be unique; there can be duplicate rows in a table.

false

We have to determine foreign keys when building an ER diagram.

false

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

WHERE and HAVING are similar because both are for

filtering

Cartesian Product

forms set of tuples of length M + N, size M * N

FROM

from which relations

What are the benefit of scripting?

great facility for integrating with other languages

define record

group of fields (addressable units)

Aggregate functions work with

group of rows

___________________ popularized indexing

hard drives

Trivial

if the right side is a subset of the left side, it's trivial

Equijoin

in a join, when theta is an equality

Natural Join

in equijoin, when a set of common attributes is eliminated

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

What are the two parts of the record id (RID)

page # , offset from foot of page

NULL values

particular entity may not have an applicable value for an attribute. - N/A: when you live in a house not an apt and don't have an apt number - unknown: missing (it exists, you just don't have it) or not known (not known whether it exists, like a home phone number).

Summarize

performs column wise computations SUMMARIZE R BY ADD SUM(D) AS DD

What are extents

physical contiguous group of pages

define Domain in terms of database

pool of values

What is scripting

programs that are executed directly from code instead of being converted to byte code

What is action?

provides directions on where to send the info when submit button is clicked

Cardinality i is similar to the number of _______ in a table Degree(arity) is similar to the number of ________ in a table

records columns

Unary is also known as a _____________ relationship

recursive

Rename

renames attributes within a specified relation

Give a brief description of DBA's role

responsible for central control of system at technical level at the direction of DA

SELECT

retrieves this information

The WHERE clause filters _____ , while the HAVING clause filters _____ .

rows, groups

Show the example of hyperlink

sarah is freshman at <a href = www.ucf.edu> UCF </a>. her major is physics sara is freshman at UCF. he major is physics

SELECT *

select all columns

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)

What is communication protocol?

set of standards that defines the structure of messages (TCP, IP,HTTP)

Union

set of tuples in M or N or both

Intersection

set of tuples which belong to both M and N

A subquery can appear in a SQL statement wherever a _____ can appear.

set of values

The word "Relational" refers to __________, while a "Relation" is a __________.

set-based mathematics from which tables are derived; a table

SET NULL/SET DEFAULT

sets foreign key value of referencing tuple

Role Name

signifies the role that a participating entity from the entity type plays in each relationship instance, and it helps to explain what the relationship means.

Schema

specifies name of relation, plus name and type of each column

DBMS requests _________File manager requests________Disk Manager performs ________________

stored record, stored page, disk I/O

schema

structure that contains descriptions of objects

what is the general form of style

style = "property_n: value_n"

In the Relational Model, data is stored in...

tables

What is method? and explain the two option

tells how the info in form being sent 1, Get: -data is displayed in address bar, not suitable to secure 2, post: - data is not displayed, suitable for secure

If a SQL statement contains a GROUP BY clause, then the SELECT clause can contain only

the GROUP BY attribute plus aggregate functions

DISTINCT

the answer should not contain duplicates

In a one-to-many (1:M) relationship, the entity on the "many" side is the

the child

Relationships

whenever an attribute of one entity type refers to another entity type, some relationship exists.

Printing output in php, what are the two basic method?

Echo: - no return value - take multiple parameters - faster than print print: - return 1 if successful - single parameter - slower than echo

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

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)

Difference

M - N is M minus the intersection of M and N

Join

M |X| N subset of the cartesian product with a join condition

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

Aggregate functions

SUM, COUNT, AVERAGE, MIN, MAX

Explain briefly the role of the DA

Senior manager, decides how the data will be stored (check your reading notes)

Name two types of indexes

Sequential access: sequential access to indexed file Direct access: access individual records in the indexed file on the basis of a given value for the indexed field e.g. "get suppliers in London"

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 the difference between an INNER JOIN and a LEFT JOIN?

The INNER JOIN returns only the rows in which the join columns match, and The LEFT JOIN gives us all the rows from the table on the left, and any rows in which the join columns match from the table on the right

Entity Set or Entity Collection

The collection of all entities of a particular entity type in the database at any point in time. The collection of entities of a particular entity type is grouped into an entity set, which is also called the extension of the entity type.

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

Pointer chains

(a.k.a. Parent/Child structure) Essentially look like linked lists. Can be both ways, e.g. doubly linked (not a great defn in book)

Explain direct and indirect data

(verify in from notes) direct-in database indirect-data that is not in database, but is materilzed

Key Attributes of an Entity Type

- An important constraint on the entities of an entity type is the key or uniqueness constraint on attributes. An entity type usually has one or more attributes whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely. Ex: SSN in EMPLOYEES. - composite attribute: sometimes several attributes together form a key, meaning that the combination of the attribute values must be distinct for each entity. Ex: registration is a key formed by two simple attributes state and number - each key attribute has its name underlined inside the oval - may have more than one key attribute - strong/regular entity type

Simple vs. Composite Attribute

- Composite attributes can be divided into smaller subparts, which represent more basic attributes with independent meanings. Composite attributes are attached to their component attributes by straight lines. - Simple attributes are not divisible

Entities and Attributes

- Each entity has attributes—the particular properties that describe it. Several types of attributes occur in the ER model: simple versus composite, single-valued versus multivalued, stored versus derived, and NULL values. - Attribute names are enclosed in ovals and are attached to their entity type by straight lines.

Relationship Type

- R among n entity types E1, E2, . . . , En defines a set of associations—or a relationship set—among entities from these entity types. Similar to the case of entity types and entity sets, a relationship type and its corresponding relationship set are customarily referred to by the same name, R. Each of the entity types E1, E2, . . . , En is said to participate in the relationship type R; similarly, each of the individual entities e1, e2, . . . , en is said to participate in the relationship instance ri = (e1, e2, . . . , en). - In ER diagrams, relationship types are displayed as diamond-shaped boxes, which are connected by straight lines to the rectangular boxes representing the participat- ing entity types. The relationship name is displayed in the diamond-shaped box

describe input type: button or submit

<input type = "button" name ="click" value = "click here" onclick = "">

Self-determination:

A -> A

Relation made up of 2 parts

- Schema - Instance

Weak Entity to Establish a 3 way ternary

- Some database design tools are based on variations of the ER model that permit only binary relationships. In this case, a ternary relationship such as SUPPLY must be represented as a weak entity type, with no partial key and with three identifying relationships. It is also possible to represent the ternary relationship as a regular entity type by introducing an artificial or surrogate key. Three binary N:1 relationships relate SUPPLY to each of the three participating entity types. Notice that it is possible to have a weak entity type with a ternary (or n-ary) identifying relationship type. In this case, the weak entity type can have several owner entity types. - Two notations for specifying structural constraints: 1- based on the cardinality ratio 2- based on the (mix, max) notation

Total Participation

- Total participation is also called existence dependency. - Ex: every entity in the total set of employee entities must be related to a department entity via WORKS_FOR - In ER diagrams, total participation (or existence dependency) is displayed as a double line connecting the participating entity type to the relationship

ADD COLUMN

- adds a data field for every tuple in the current instance of the relation - this value is initialized to null

Stored versus Derived Attributes

- derived: determined from stored values; "derivable from" stored attributes. can also be derived from related entities. - Derived attributes are shown in dotted ovals. - stored: inputted value

Weak Entity Type

- has no key - are identified by being related to specific entities from another entity type in combination with one of their attribute values. We call this other entity type the identifying or owner entity type, and we call the relationship type that relates a weak entity type to its owner the identifying relationship of the weak entity type. - normally has a partial key - A weak entity type always has a total participation constraint (existence dependency) with respect to its identifying relationship because a weak entity cannot be identified without an owner entity. **However, not every existence dependency results in a weak entity type. For example, a DRIVER_LICENSE entity cannot exist unless it is related to a PERSON entity, even though it has its own key (License_number) and hence is not a weak entity. -In ER diagrams, both a weak entity type and its identifying relationship are distinguished by surrounding their boxes and diamonds with double lines

Partial Participation

- meaning that some or part of the set of employee entities are related to some department entity via MANAGES, but not necessarily all. Not all employee manage. - partial participation is represented by a single line

What are the three parts of URI

- name of protocol used to access resources (http) - name of the host computer www.ucf.edu - name of the resource

Single-Valued versus Multivalued Attributes

- single-valued: a single value for a particular entity - A multivalued attribute: different numbers of values for an attribute. They may have lower and upper bounds to constrain the number of values allowed for each individual entity. Multivalued attributes are displayed in double ovals.

Cardinality Ratio

- specifies the maximum number of relationship instances that an entity can participate in - For example, in the WORKS_FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N, meaning that each department can be related to (that is, employs) any number of employees (N),9 but an employee can be related to (work for) at most one department (1) - binary relationship (2 things) is specified by attaching a 1, M, or N on each participating edge

Participation Constraint

- specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This constraint specifies the minimum number of relationship instances that each entity can participate in and is sometimes called the minimum cardinality constraint.

What are the purpose of SQL standards?

- specify syntax -define data structure - enable portability - specify minimal complete standard - allow for later growth

XML elements

- tags -<ELM> </ELM> - can be nested -case sensitive

Partial Key

- which is the attribute that can uniquely identify weak entities that are related to the same owner entity - The partial key attribute is underlined with a dashed or dotted line

What are the characteristics (properties) of a Relation?

-All attributes values are atomic -Tuples are unorder (top to bottom) -Attributes are unorder (left to right) -There are no duplicate tuples

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

A table is said to represent a relation, if the table agrees on ...

-Each column has an underlying type -All atomic values are of the column type -Row and column orderings are irrelevant -Duplicate Rows are forbidden

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 does stateless mean?

-no session -every message is completely self contained -no previous interaction is remembered -tradeoff between ease of implementation/application

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 three levels of style sheets?

1 inline - specified for specific occurrence of a tag, appear in tag itself 2 document level style sheets - apply to whole document in which they appear, head of document 3 external style sheets - can be applied to any number of documents, separate file, any server

What are the four web data formats? explain each brief.

1, HTML: presentation language for internet 2, XML: self-describing, hierarchal data model 3, DTD: data type definition is standardizing schemas for xml 4, XSLT: extensible stylesheet language transformation

what are the four possible controls for ensuring data integrity?

1, update restrict 2, update cascade 3, update set null 4, update set default

ER Model Concepts

1- Entities and Attributes 2- Entity Types and Key Attributes 3- Relationships and Relationship Types 4- Structural Constraints and Roles 5- Weak Entity Types

Database Design Process

1- requirements collection and analysis -During this step, the database designers interview prospective database users to understand and document their data requirements--written set of users' requirements, functional requirements of the application (retrievals and updates). 2- conceptual schema (conceptual design) -The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints; these are expressed using the concepts provided by the high-level data model. 3- actual implementation of the database, using a commercial DBMS -Logical design or data model mapping; its result is a database schema in the implementation data model of the DBMS. Data model mapping is often automated or semiautomated within the database design tools. 4- physical design phase -The internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files are specified.

thin arrow

1-to-many relationship

Join without a given condition

1. Follow relations with common attributes 2. Relations without common attributes (yields cartesian product)

What are the 3 level data base management system architecture

1. Internal 2. Conceptual 3. External (user-level)

Join with a given condition

1. follow given condition

The principles of relational database were originated in ________ by ___________.

1969-1970, Edgar Codd

Common HTTP response status?

200 ok: request succeeded 400 bad request: the request could not fulfilled 404 not found: request object does not exist 505 HTTP version not supported: protocol is not supported by server

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

What are the entity reference for XML

< = lt > = gt & = amp " = quot ' = apos

xml comments

<!- comment ->

HTML basic constructs

<html> <head> -> info on page, title <body> -> display <h1>section</h1> -> six level <ul> <li>item</li> </ul> </body> </head> </html>

A Composite Key is

A key that is made up of more than one attribute

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

To implement a Many-to-Many relationship, you do this

Add an intersection table between the two tables. The intersection table will contain a foreign key to each of the two tables being linked.

nontrivial

All other dependencies are called

KSDS is essentially a _________ and can be ___________ length

B-tree, variable length

Relationships of Higher Degree

Binary and ternary relationships.

What was the first relational database?

Ingress

When a table has more than one attribute that could each serve as the table's primary key (for example EmployeeNum and SSN), we call these

Candidate Keys

"One-to-one", "one-to-many", and "many-to-many" (or 1:1, 1:M, and M:M) are examples of

Cardinality Ratio

What is the cardinality and degree of a relation?

Cardinality: number of tuples in a table Degree: number of attributes

The mathematically-based framework of Codd's Relational Model is called

Conceptual Data Model

In SQL, how do you delete a row from a table?

DELETE FROM tableName WHERE column = value;

Name the four types of relational constraints

Domain

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 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.

_____________ allows DBMS to ignore detail of "page I/O" and think in terms (for the most part) in terms of files and records

File Manager

What is an index? What are the pros and cons?

File in which each record consists of precisely two values; data value and pointer. speed ups retrieval, but slows updates

the difference between HTML vs XML

HTML: - supports fixed set tags - not enough tags to describe the structures of content of more general app XML: - allows users to define new tags to structure any type of data or document - it makes database systems more lightly integrated into web app

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 SQL, how do you add a new row to the table named 'Employee'?

INSERT INTO TableName (column list) VALUES (value list);

Union

If A -> B and A -> C, then A -> BC

Transitivity:

If A -> B and B -> C, then A -> C

Composition

If A -> B and C -> D, then AC -> BD

Augmentation

If A -> B, then AC -> BC

Decomposition

If A -> BC, then A -> B and A -> C

Name some advantages of using a Surrogate Key

Improves referential integrity Surrogate keys are always unique Since we can choose the data type of a surrogate key, we can choose the type that's most efficient for keys.

In dynamic hashing, what happens if a bucket overflows?

In this case, 52 was added. Prior to that, the depth was 2

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

If we want to save multiple e-mail addresses for each person in our Contact table, which data pattern would we use?

Multivalued Attribute pattern

Explain dense vs. nondense indexing

NONDENSE: does not contain an entry for every record in the indexed field ----> will occupy less space DENSE: the reverse

Complex Attributes

Notice that, in general, composite and multivalued attributes can be nested arbitrarily. We can represent arbitrary nesting by grouping components of a composite attribute between parentheses ( ) and separating the components with commas, and by displaying multivalued attributes between braces { }

grouping functions

ORDER BY, GROUP BY,

Division

P:Q (P divided by Q) is the set of p' (P has attributes p' + q', Q has attributes q') tuples such that all q' in Q are in a tuple p'q' in P

How does the disk manager know where the various page sets are?

PAGE ZERO (a.k.a. disk table of contents, disk directory) contains list of page sets currently in existence on disk, together with a pointer to the first page of each page set

Who created the entity model and when?

Peter Chen 1976

Every table must have a

Primary Key

describe input type: checkbox

Programming Experience: <br> <input type="checkbox" name ="C" value="C"> C<br> <input type="checkbox" name ="C++" value="C++"> C++<br> <input type="checkbox" name ="J" value="Java"> Java<br> <input type="checkbox" name ="P" value="Python"> Python<br>

In SQL, how do you select all the columns from a table named "Employee"?

SELECT * FROM Employee

In SQL, how do you list all the rows of the Employee table and FamilyMember table that match on the employeeId attribute?

SELECT * FROM Employee JOIN FamilyMember ON Employee.employeeId = FamilyMember.employeeId

In SQL, how can you return all the rows from a table named "Employee" sorted in descending order by firstName?

SELECT * FROM Employee ORDER BY firstName DESC

In SQL, how do you select all the rows from a table named "Employee" where the value of the column "firstName" is "John"?

SELECT * FROM Employee WHERE firstName = 'John'

What query would we use to select all the employees from the Employee table whose first name is the same as his last name?

SELECT * FROM Employee WHERE firstName = lastName

What SQL statement is used to return only different values from the Employee table, eliminating duplicates?

SELECT DISTINCT LastName FROM Employee

In SQL, how do you select a column named "firstName" from a table named "Employee"?

SELECT firstName FROM Employee

Mandatory Participation means

The minimum cardinality of a relationship is one or more

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.

What is the main function of the Disk Manager?

To allow the file manager to ignore detail of physical I/O and think in terms of "page I/O" instead This is referred to as Page Management

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.

In SQL, how do you change the value of an attribute in an existing row?

UPDATE Table SET columnA=value WHERE column = someValue;

Name the major components of the database system

USER SOFTWARE HARDWARE DATA

what is URI?

Uniform Resource Identifier - identify the resources on the internet

Structural Constraints

We will refer to the cardinality ratio and participation constraints, taken together, as the structural constraints of a relationship type

What is a database?

a collection of data items

Entity Set

a collection of similar entities that all have the same attributes

Integrity Constraint

a condition that must be true for any instance of the database

Functional Dependence

a many to one relationship from one set of attributes to another within a given relvar X -> Y | X functionally determines Y | X: determinant, Y: dependent For every X, there is only one Y

Catalog

a set of schemas that constitute the description of database

Instance

a table with rows and columns

Extend

adds additional attribute(s) whose value is obtained by evaluating some expression EXTEND M ADD(C+2) AS CC

With

allows you to name a subquery

CASCADE

also delete all tuples that refer to that deleted tuple

ALTER TABLE

alter the relation in some way

What is "Crow's Foot Notation"?

an Entity-Relationship Diagram notation that expresses the relationship between two entities as a line between the two boxes.

An ID-Dependent Entity is

an entity whose key contains the key of another entity

Entity

an object within an entity set that has the same attributes but is distinguishable from other entities in the same set

What is implication for application?

any state information neeed to be encoded in every HTTP request and response

Physical data independence means

applications are insulated from the underlying hardware and software of the host system

Degree

as the number of participating entity types

Relationship

association among two or more entities

Referential Integrity means:

assuring that the connections between tables remain valid

Tuples are triples that contain _________ , ___________ and _________

attribute name, attribute type, value

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

weak entity

can be identified uniquely only by considering the primary key of another entity

If a table contains two attributes that are each unique on their own, and either one could serve as a primary key, these attributes are called

candidate keys

Recursive Relationships or Self-Referencing Relationships

in some cases the same entity type participates more than once in a relationship type in different roles. In such cases the role name becomes essential for distinguishing the meaning of the role that each participating entity plays.

What is the Data Dictionary?

information about database; all various, schemata, mappings

What is the most common type of join?

inner join

INSERT INTO

insert a single tuple

What is existence dependency?

instance of one entity that cannot exist without the existence of some other related entity

HAVING

introduces condition on an aggregation of rows when grouping

Name two properties of a primary key

is a unique identifier cannot be null

<link> tag

is used to specify that the browser is to fetch and use an external style sheets

Why is SQL considered non-procedural language?

it does not loop

The hallmark of an ID-dependent entity is that

it has the identifier (key) of another entity as part of its own identifier.

__________ is a cartesian product with a condition

join

Semijoin

join followed by projection

what are three xml extensible markup language?

language: - way of communicating info markup: -notes that describe your data or language extensible: -limitless ability to define new language or data sets

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

The Recursive Pattern (1:1, 1:M, M:M) can model a

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

Data manipulation language (DML)

manipulation of data in database. (update,select,insert)

line

many-to-many relationship

The Association data pattern is a slightly expanded version of the ______ relationship, but with the addition of extra attributes in the ______ table which describe the relationship.

many-to-many, intersection

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

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

Degree

the number of participating entity types

ER (Entity-relationship model) diagrams

the schema for this database application can be displayed by means of the graphical notation

What is meant by the Domain of an attribute?

the set of values that an attribute draws from

A Foreign Key column is not a key itself. Each row in the child table contains the same value in its Foreign Key column as in the Primary Key column of its Parent row. As a result, rows in the two tables are linked together. This works because:

the two tables share a common value

The primary purpose of the Subtype/Supertype relationship is?

to eliminate type-inappropriate nulls

A relationship name in an ER diagram can be different depending on the direction you read the relationship

true

In a SQL query containing a GROUP BY clause, filtering of individual rows occurs before filtering and sorting of groups.

true

The relational model does not directly support many-to-many relationships.

true

key

uniquely identifies an entity within a set. labeled with an underline

Which SQL statement is used to update data in a database?

update

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

describe input type: text

username:<br> <input type = "text" name="uname" value"">

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

Relationship Instances

where each ri associates n individual entities (e1, e2, . . . , en), and each entity ej in ri is a member of entity set Ej, 1 ≤ j ≤ n. Hence, a relationship set is a mathematical relation on E1, E2, . . . , En; alternatively, it can be defined as a subset of the Cartesian product of the entity sets E1 × E2 × . . . × En.

How are weak entities relationships visually shown on an E/R diagram?

with a double lined diamond, as shown

Projection

π : subset of relation over selected attributes e.g, M [ A, B ] ***remove duplicates

Restriction/Selection

σ: subset of tuples with certain value on a specified attribute (condition θ) R WHERE C