ECP3076 tut2

1. Key defines entity relationships. Primary key, secondary key, foreign key, superkey, candidate key.
2. …
4. SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE, same commands. Note* SLECT in relational algebra only means selecting entities from an entity set, while SELECT in SQL is a command that has much wider use.
5. PRODUCT produces a list of all possible pairs of rows from two tables.
SELECT yields values for all attributes found in a table. It yields a horizontal subset of a table. (command: select * from table1, table 2)
PROJECT produces a list of all values for selected attributes. It yields a vertical subset of a table
6. all joins are implemented with SELECT. The difference is in the WHERE clause. With a natural join, you are always comparing attributes with the same name, using the = comparison
Select * from table1, table2 where table1.t1_ID = table2.t1_ID
An equi join, u use table columns which are permitted to be different, but still with the = comparison
Select * from table1, table2 where t1_ID = t1_ID
A theta join is where the comparison operatior may be some other thing
Select * from table1, table2 where T1_A < T1_B


Section B
b. DIR_NUM
c. 1 to many, no
d. The foreign key must hav a null value or matches primary key values in the PLAY TABLE.
The PLAY table has a foreign key which is the primary key of the DIRECTOR table. Entering data into the PLAY table requires that the foreign key hav a matching value to the PK of the DIRECTOR table. This ensures that a PLAY must be directed by a listed/recorded director in the database else it must be left null (it is possible for a play to exist without a director..)

Part C:
create database test;
connect to test;
create table student1
(
stud_id char(10) not null,
stud_name varchar(50),
stud_hometown varchar(50),
stud_state varchar(50),
stud_religion varchar(50),
stud_birthday date,
primary key (stud_id)
);

insert into student1 values ('1071112489', 'Edwin', 'Batu Berendam', 'Melaka', 'Buddhist', '1989-03-25');
insert into student1 values ('1061111659', 'Adrian', 'Sin Hoe Garden', 'Melaka', 'Buddhist', '1989-07-07');

select * from student1;
select * from student1 where stud_name = 'Edwin';

create table student2
(
stud_id char(10) not null primary key,
stud_name varchar(50),
stud_hometown varchar(50),
stud_state varchar(50),
stud_religion varchar(50),
stud_birthday date
);