Week 4 - Databasae

What are the pitfalls of Relational-Database Design?

A bad design may be associated with the following undesirable properties: Repetition of information: Repeating information wastes space. Furthermore, it complicates updating the database. Inability to represent certain information: Certain information cannot be represented directly. This introduces difficulty in insertion and deletion.

Name few properties of relation.

A relation has the following properties: the relation has a name that is distinct from all other relation names in the relational schema; each cell of the relation contains exactly one atomic (single) value; each attribute has a distinct name; the values of an attribute are all from the same domain; each tuple is distinct; there are no duplicate tuples; the order of attributes has no significance; the order of tuples has no significance, theoretically. (However, in practice, the order may affect the efficiency of accessing tuples.)

Describe structure of relational databases.

A relational database consists of a collection of tables, each of which is assigned a unique name. A row in a table represents a relationship among a set of values. Since a table is a collection of relationships, there is a close correspondence between the concept of table and the mathematical concept of relation. Mathematically, a relation is a subset of a Cartesian product of a list of domains. The only difference is that here names are assigned to attributes, whereas mathematicians rely on numeric values, using the integer 1 to denote the attribute whose domain appears first in the list of domains, 2 for the attribute whose domain appears second, and so on. Because tables are essentially relations, the mathematical terms relation and tuple shall be used in place of the terms table and row. Generally, a relation r is declared on a relation schema R = (A, B, C) as r(R).

What does decomposition of relational schema mean?

A relational schema that has many attributes should be decomposed into several schemas with fewer attributes. Careless decomposition may lead to another form of bad design. Let R be a relation schema. A set of relation schemas {R1, R2, ..., Rn} is a decomposition of R if the unions of these R1, R2, ..., Rn results in R. That is, {R1, R2, ..., Rn} is a decomposition of R if, for i = 1, 2, ..., n, each Ri is a subset of R, and every attribute in R appears in at least one Ri.

What is a tuple?

A tuple is a row of a relation.

What do you mean by general constraints. give an example.

Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise. It is also possible for users to specify additional constraints that the data must satisfy. For example: A student can enrol in maximum 2 courses in a study period.

Name five types of integrity constraints.

Domain constraints Entity integrity Referential integrity Multiplicity and General constraints

What is Entity Integrity?

In a base relation, no attribute of a primary key can be null.

What is the degree of a relation in relational model?

In relational model, the degree of a relation is the number of attributes it contains.

What is functional dependency?

Let R be a relation schema, and a and b are subsets of R. The functional dependency a -> b holds on R if, in any legal relation r(R), for all parits of tuples t1 and t2 in r such that t1[a] = t2[a], it is also the case that t1[b] = t2[b].

What are the desirable properties of decomposition?

Lossless-join Decomposition When decomposing a relation into a number of smaller relations, it is crucial that the decomposition be lossless. Repetition of Information The lack of redundancy in the decomposition is desirable. The degree to which this lack of redundancy can be achieved is represented by several normal forms. Dependency Preservation When an update is made to the database, the system should be able to check that the update will not create an illegal relation.

Describe referential integrity constraints.

Referential integrity is a condition which ensure that a value that appears in one relation for a given set of attributes also appears for certain set of attributes in another relation. Let r1(R1) and r2(R2) be relations with primary keys K1 and K2, respectively. A subset f of R2 is a foreign key referencing K1 in relation r1 if it is required that, for every tuple t2 in r2, there must be a tuple t1 in r1 such that t1[K1] = t2[f]. Requirements of this form are called referential integrity constraints, or subset dependencies. For a referential-integrity constraint to make sense, either f must be equal to K1, or f and K1 must be compatible sets of attributes.

What is relational model?

Relational model is one of the most popular and most widely used logical models for database design. In the relational model, all data is logically structured within relations (tables). Each relation has a name and is made up of named attributes (columns) of data. Each tuple (row) contains one value per attribute.

What is the goal of a Relational-Database Design?

The goal of a relational-database design is to generate a set of relation schemas that allows user to store information without unnecessary redundancy, yet also allows user to retrieve information easily.

What is intension of a relation?

The structure of a relation, together with a specification of the domains and any other restrictions on possible values, is sometimes called its intension. It is usually fixed unless the meaning of a relation is changed to include additional attributes.

What is primary key?

The term primary key is used to denote a candidate key that is chosen by the database designer as the principal means of identifying tuples or records within a relation or table.

What is extension (or state) of a relation?

The tuples are called the extension (or state) of a relation, which changes over time.