Question

1. Theory. Describe the ERD to Relations algorithm. We are interested in the steps and a brief description.
2. Theory. What is an integrity constraint? Provide an example for an airline system.
3. Set Theory. Let S = {d, b} and T = {1, 7, 3}, computer:
   • T X S =
   • S X T =
   • |S| =
   • S − T =
4. Transactions. What are the 4 properties of transactions? Explain each of them succinctly.

Answer 1

1.

1. Entities and Simple Attributes:

An entity type within ER diagram is turned into a table. You may preferably keep the same name for the entity or give it a sensible name but avoid DBMS reserved words as well as avoid the use of special characters.
Each attribute turns into a column (attribute) in the table. The key attribute of the entity is the primary key of the table which is usually underlined. It can be composite if required but can never be null.

Taking the following simple ER diagram:

The initial relational schema is expressed in the following format writing the table names with the attributes list inside a parentheses as shown below for

Person Name Phone Email Lastname

Persons( personid , name, lastname, email )

Persons and Phones are Tables. name, lastname, are Table Columns (Attributes).

personid is the primary key for the table : Person

2. Multi-Valued Attributes

A multi-valued attribute is usually represented with a double-line oval.

Phone Person Name

If you have a multi-valued attribute, take the attribute and turn it into a new entity or table of its own. Then make a 1:N relationship between the new entity and the existing one. In simple words. 1. Create a table for the attribute. 2. Add the primary (id) column of the parent entity as a foreign key within the new table as shown below:

Persons( personid , name, lastname, email )
Phones ( phoneid , personid, phone )

personid within the table Phones is a foreign key referring to the personid of Persons

3. 1:1 Relationships

To keep it simple and even for better performances at data retrieval, I would personally recommend using attributes to represent such relationship. For instance, let us consider the case where the Person has or optionally has one wife. You can place the primary key of the wife within the table of the Persons which we call in this case Foreign key as shown below.

Name Wife Have Email Lastname Person Phone Name

Persons( personid , name, lastname, email , wifeid )
Wife ( wifeid , name )

Or vice versa to put the personid as a foreign key within the Wife table as shown below:

Persons( personid , name, lastname, email )
Wife ( wifeid , name , personid)

For cases when the Person is not married i.e. has no wifeID, the attribute can set to NULL

4. 1:N Relationships

This is the tricky part ! For simplicity, use attributes in the same way as 1:1 relationship but we have only one choice as opposed to two choices. For instance, the Person can have a House from zero to many , but a House can have only one Person. To represent such relationship the personid as the Parent node must be placed within the Child table as a foreign key but not the other way around as shown next:

It should convert to :

Addres House Num IN Has Email Lastname Person Phone Name

Persons( personid , name, lastname, email )
House ( houseid , num , address, personid)

5. N:N Relationships

We normally use tables to express such type of relationship. This is the same for N − ary relationship of ER diagrams. For instance, The Person can live or work in many countries. Also, a country can have many people. To express this relationship within a relational schema we use a separate table as shown below:

It should convert into :

Addres Country Num Has Email Lastname N Person Phone Name

Persons( personid , name, lastname, email )
Countries ( countryid , name, code)
HasRelat ( hasrelatid , personid , countryid)

Relationship with attributes:

It is recommended to use table to represent them to keep the design tidy and clean regardless of the cardinality of the relationship.

2.

• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.

For example, consider the following instances of three relations:

	Passenger		Flight		BookedOn
	id name city 001 Tom Brady San Mateo, CA 002 David Ortiz Santo Domingo, Dom. Rep 003 Kevin Youkilis Cincinnati, Oh 004 Ray Allen Riverside, CA 005 Kevin Garnett Greenville, SC 006 Zdeno Chara Trencin, SVK		number origin destination DL5882 Boston, MA Indianapolis, IN BA215 London, United Kingdom Boston, MA AA573 Boston, MA Miami, FL LH422 Frankfurt, Germany Boston, MA UA717 Boston, MA Los Angeles, CA UA898 Beijing, China Boston, MA		person flight 005 UA898 002 AA573 005 UA717 006 BA215 001 DL5882 004 DL5882

Notes:

• Every person in the Passenger relation has a unique value for the id attribute.
• Every flight in the Flight relation has a unique value for the number attribute.
• The BookedOn relation captures the relationships between people and the flights on which they are booked.
  • Example: the tuple (001, DL5882) in BookedOn tells us that Tom Brady (001) is booked on a flight from Boston to Indianapolis (DL5882).

NOTE: As per HOMEWORKLIB POLICY, I am allowed to answer only 2 questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.