Question : 1 Assume that a bookshop has database with the following scheme of six relations :

                Authors (au_id, au_laname, au_frame, phone, address)
                Publishers (pub_id, pub_name, city, state, country)
                sales (stor_id, ord_name, stor_address, qty, title id)
                stores (stor_id, stor_name, stor_address, city, state, zip)
                Title author (au_id, title_id, royaltyper, au_ord)
                Titles (title_id, title, type, pub_id, price, pubdate)

Write the following quries in relation algebra :

i- List the authors first and last names for the book titles ' life without fear '.
ii- List the title of the books sold at Barnum's. (Stor_name)
iii- Give the last name (au_lname) and au_ids of the authors whose have sold in the store named Barnum's. Assign an alias to your answer.
iv- Using your alias from the previous problem, give the last names and au_ids of the authors whose books have not sold in the store name Barnum's.
v- List the titles of books, which have not sold any copies at any store (no books sold at all).
vi- List titles of the books which have only sold at Barnum's. (Do not list the books which hava not sold anywhere).
vii- List the titles of the books, which lhave sold at both Barnum's and bookbeat. (Two stor_names).
viii- List the titles and store_names for each book when the book has sold and the title only (with null for the stor_name) if the book has not sold. (use an outer join).
ix- List the stor_ids of the stores which have sold all books published bu New Moon Books (pub_name).
x- List the titles of the books, which have sold at all stores.

Ans:
Query (i) : List the authors first and last names for the book titled 'life Without Fear'.

SQL .
Select au_lname, au_fname
From Authors
where au_id in (select au_id
from titleauthor
Where title_id in (select title_id
from titles
Where upper(title)='Life Without Fear'
)
)

Query (ii) : List the titles of the books sold at Barnum's. (stor_name)

SQL.
select title
from titles
Where title_id in (select title_id
from sales
where stor_id in(select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)

Query (iii) : List titles of the books sold at Barnum's. (stor_name)

SQL.
select distinct au_lname "Last Name", au_id "Author ID"
from Titleauthor
where title_id in(select title_id
from sales
where stor_id in (select stor_id
from stores
where upper(stor_name)='Banum's'
)
)
Query (iv) : Using your alias from the previous problem, give the lastnames and au_ids of authors whose books have not sold in the store named Barnum's.

SQL.
(select distint au_lname"Last name, au_id"Author id"
from titleauthor
)
minus
(select distinct au_lname "last name", au_id "author id"
from titleauthor
where title_id in (select title_id
from sales
where stor_id in (select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)
)

Query (v) : List the titles of books, which have not sold any copies at any store (no books sold at all).

SQL.
(select title from titles)
minus
(select title
from titles
where title_id in (select title_id
from sales
)
)

Query (vi) : List the titles of books, which have only sold at Barnum's (Do not list the books, which have sold anywhere).

SQL.
Select title
from titles T
where not exists(
(
select stor_id
from sales S
where S.title_id=T.title_id
)
minus
(
select stor_id
from stores
where upper(stor_name)='Barnum's'
)
)

Query(vii) : List the titles of the books, which have sold at both Barnum's and bookbeat (Two stor_names)

SQL.
select titles
where title_id in
(
(
select title_id
from sales
where stor_id in (select stor_id
from stores
where
upper(stor_name)='Bookbeat'
)
)
)

Query(viii) : List the titles and stor_names for each book when the book has sold and the title only (with null for the stor_name) if the book has not sold(Use an outor join)

SQL.
select title, stor_name "stor name"
from titles, stores
where titles.tiltle_id=stores.title_id(+);

Query(ix) : List the stor_ids of stores which have sold all books published by New Moon books (pub_name)

SQL.
select stor_id
from stores S
where not exists
(
(select title_id
from titles
where titles
where pub_id in (select pub_id
from publishers
where upper(pub_name)='New Moon books'
)
)
minus
(
select title_id
from stores SI
Where S.store_idSI.store_id
)
)

Query(x) : List the titles of the books, which have sold at all stores.

SQL.
select title
from title T
where not exists
(
(
select stor_id
from stores
)
minus
(
select store_id
from sales S
where S.title_id=T.Title_id)
)








Question : 2 Define the two principle integrity rules for the relational model. Discuss why it is disarable to enforce these rules?

Ans: 
Intrgrity rules :-
The relational model includes two general integrity rules. These rules implicitly or explicitly define the set of consistent database states, or changes of sate, or both. Other integrity constraints can be specific, for example, in terms of dependencies during database design.

The principle integrity for the ralational model.
Entity integrity (Integrity Rule 1):-
In a relational database, entity integrity is a property that ensure that no records are duplicated and that no attributes that make up the primary key are NULL. It is one of the properties necessary to ensure the consistency of the database.
Entity Ingegrity ensures that are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.

In a base table no column of a primary key can be null.

                        Intigrity rules are implicity or explicity define the set of consistent database states or changes of states or both.

Referential Integrity (Integrity Rule 2):-
Referential integrity in a relational database is consistency between coupled tables. Referential integrity usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key.

If a foreign key exists in a table, either the foreign key value must match wholly null.




Question : 3 What is a view? Discuss the difference between a view and a base relation. Explain what happens when a user accesses a batabase through a view.

Ans:
View: A relation that is a part of the physical databse, (e.g. A virtual relation) is made available to the users, is called a view.

Difference between a view and a base relation :-