Test 2

To change a foreign key constraint via SQL:

*ALTER TABLE* tableName *DROP CONSTRAINT* FK_constraintName; *ALTER TABLE* tableName *ADD CONSTRAINT* FK_name *FOREIGN KEY* (FKatt) *REFERENCES* refTable(RefAtt) *ON DELETE* deleteAction *ON UPDATE* updateAction;

How do you create a table in SQL?

*CREATE TABLE* RelationName ( AttributeName1 type, AttributeName2 type, ... AttributeNamen type );

how do you remove a tuple from a table?

*DELETE FROM* table-name WHERE condition *DELETE FROM* table-name *WHERE* condition *and* condition...

how do you insert values into a table?

*INSERT INTO* table-name *VALUES* (value1, ...,...,...), (value1, ...,...,...);

what are the different attribute constraints and give examples of how to reference them when creating a table

*Not Null* DID CHAR(4) NOT NULL, *Default Values* GPA DECIMAL(4,3) DEFAULT 0.00, *Primary Key* DID CHAR(4) PRIMARY KEY, Primary key only works for single PK's and assumes not null for you.

how do you update a tuple from a table?

*UPDATE* table-name *SET* att = value, att = value... *WHERE* condition

an attribute (or set of attributes) FK in a relation R1 is a foreign key if it satisfies both of the following conditions:

1. (Each of the attributes in) FK has the same domain (or type) as (one of the attributes of) the primary key, PK, of another relation R2. 2. A value of (an attribute of) FK in a tuple t1 of R1 either occurs as the value of (an associated attribute of) PK for some tuple t2 in R2, or is null. In the first case we say that the tuple t1 references the tuple t2.

Key constraints serve two purposes:

1. Relations are mathematical sets and sets do not allow duplicate values. The key constraint assures us that relations will reflect this property of sets. 2. Relational data models are used to represent abstractions of real situations; in particular, the tuples in some relations are going to represent objects from that real situation. The key constraint assures us that the relational model is capable of distinguishing between objects.

5 properties of a relational database

1. each relation within a database must have a name 2. each attribute of each relation must have a name 3. each attribute must have an associated type 4. each realtion must have one or more attributes whose combined values are sufficient to distinguish (or identify) each tuple in the relation. (PK) 5. Some relations may have an attribute whose values are expected to match those of the primary key from another relation (FK)

what are the three types of normal forms?

2NF 3NF BCNF (Boyce-codd)

transitive dependency

A condition where A, B, and C are attributes of a relation such that if A -->B and B -->C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C). A condition in which an attribute is dependent on another attribute that is not part of the primary key.

partial dependency

A functional dependency A --> is a partial dependency if there is some attribute that can be removed from A and yet the dependency still holds. In normalization, a condition in which an attribute is dependent on only a portion (subset) of the primary key.

BCNF

A relation is in BCNF if and only if every determinant is a candidate key.

2NF

A relation that is in first normal form and *every non-primary-key attribute is fully functionally dependent on the primary key.* all non-key attributes are fully functionally dependent on the PK.

what are the character string types?

CHAR(n), CHARACTER(n) VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

what are the date/time types?

DATE(YYYY-MM-DD) TIME(HH:MM:SS) DATETIME

functional dependency

Describes the relationship between attributes in a relation. For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A --> B), if each value of A is associated with exactly one value of B. (A and B may each consist of one or more attributes.)

Referential Integrity Constraint

Given two relations R and S, if relation R has a foreign key that references the primary key of relation S, then for any tuple in R, the value of the foreign key of of this tuple must be equal to the value of the primary key of a tuple of S or else be (entirely) null.

what are the most used types

INTEGER DECIMAL(p,s) CHAR(n) -fixed length VARCHAR(n) - max length

what are the numeric types?

INTEGER, INT, SMALLINT FLOAT, REAL, DOUBLE PRECISION DECIMAL(i,j), DEC(i,j), NUMERIC(i,j)

Entity Integrity Constraint

No attribute of a primary key can accept null values.

what are the ways to make PK and FK constraints?

PRIMARY KEY (att1, att2,...) or CONTSTRAINT constraint-name PRIMARY KEY (att1, att2,...) PRIMARY KEY (att) REFERENCES table-name(att) or CONTSTRAINT constraint-name FOREIGN KEY (att) REFERENCES table-name (att)

what are 4 update actions

SET NULL SET DEFAULT CASCADE if a referenced tuple is deleted, delete all the referencing tuples; if the value of the primary key of a referenced primary key is changed, change the value of the foreign key to the new key value for all referencing tuples RESTRICT for ON DELETE, will not allow a tuple that is referenced via a foreign key reference to be deleted. for ON UPDATE, will not allow the primary key value of a tuple that is referenced via a foreign key reference to be changed

how to move from 1NF to 2NF

The normalization of 1NF relations to 2NF involves the removal of partial dependencies. If a partial dependency exists, we remove the partially dependent attribute(s) from the relation by placing them in a new relation along with a copy of their determinant.

SQL

a language developed for relational database systems that has become the de facto standard for working with relational databases.

data modeling conceptual data model implementation data model

begin by abstracting data-focused information about an enterprise (in contrast to a behavior-centered focus, for example) using this to construct a *conceptual data model* of the enterprise that uses concepts that are close to the way users perceive data and their relationships. From there we map the conceptual data model to an *implementation data model* (such as a relational schema) that is more closely related to how the data will ultimately be stored on a persistent storage medium

how do you alter a table and add contrstraints?

e.g. ALTER TABLE DEPARTMENT ADD CONSTRAINT FK_DEPARTMENT_ChairID FOREIGN KEY (ChairID) REFERENCES FACULTY(FID)

how can 2NF be transformed to 3NF?

finding any non-key attributes which are transitively dependent on the key and placing them and their determinants in a new relation

attribute whose values are expected to match those of the primary key from another relation

foreign key

Explain functional dependency in own words

given A-->B A will only have one instance of B. e.g. A=StudentID B=StudentName StudID(114) will only pertain to one StudName(Bob) but the StudName(Bob) may relate to multiple StudID's b/c bob is not unique.

What is full functional dependence?

if it is Functionally Dependent on that attribute and not on any of its proper subset. (No partial dependencies)

3NF

if it is in 2NF and no non-key attribute is transitively dependent on the key. "All non-key attributes depend on the key, the whole key, and nothing but the key."

waht are the main types available for attributes in SQL?

numeric Character string date/time

primary key

one or more attributes whose combined values are sufficient to distinguish (or identify) each tuple in the relation.

An attribute of a primary key is called a ________ _________.

prime attribute

what are the four problems you can have with a scheme?

redundancy update anomalies insertion anomalies deletion anomalies

Denormalization

refers to a refinement to the relational schema such that the degree of normalization for a modified relation is less than the degree of at least one of the original relations.

schema

the organization of the database, including the set of relations, and for each relation the names of the attributes, their types, its primary key, and any foreign keys.

Logical database design

the process for determining how to take the data from an enterprise that wants to develop a database to abet its work and to arrange it into the structures supported by the database management system to be used. (in DBMS deciding on the relations to be used, their attributes, primary keys and foreign keys.)

Database normalization

the process of structuring one or more relations in a relational database schema in accordance with a series of so-called normal forms in order to reduce the amount of redundant data that is, or would be, stored in the database and as a result to improve the integrity of that data. systematic way of ensuring a minimal amount of data redundancy, while at the same time avoiding the various types of anomalies

what is the key constraint?

the relational model requires every relation to have at least one candidate key. 1. Two distinct tuples cannot have identical values in all the attributes of the candidate key 2. No subset of the set of attributes in a key can also serve as a candidate key.