Page 26 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 2: Database Relational Model

If we wanted to retrieve all of the Orders for Customer 1234567890, we could query the database Notes
to return every row in the Order table with Customer ID 1234567890 and join the Order table to
the Order Line table based on Order No.
There is a flaw in our database design above. The Invoice relvar contains an Order No attribute.
So, each tuple in the Invoice relvar will have one Order No, which implies that there is precisely
one Order for each Invoice. But in reality an invoice can be created against many orders, or
indeed for no particular order. Additionally the Order relvar contains an Invoice No attribute,
implying that each Order has a corresponding Invoice. But again this is not always true in the
real world. An order is sometimes paid through several invoices, and sometimes paid without
an invoice. In other words there can be many Invoices per Order and many Orders per Invoice.
This is a many-to-many relationship between Order and Invoice (also called a non-specific
relationship). To represent this relationship in the database a new relvar should be introduced
whose role is to specify the correspondence between Orders and Invoices:

OrderInvoice (Order No, Invoice No)

Now, the Order relvar has a one-to-many relationship to the OrderInvoice table, as does the
Invoice relvar. If we want to retrieve every Invoice for a particular Order, we can query for all
orders where Order No in the Order relation equals the Order No in OrderInvoice, and where
Invoice No in OrderInvoice equals the Invoice No in Invoice.

Task Advantages of NULL constraints in DBMS.

2.1.6 Set-theoretic Formulation

Basic notions in the relational model are relation names and attribute names. We will represent
these as strings such as “Person” and “name” and we will usually use the variables r, s, t …and
a, b, c to range over them. Another basic notion is the set of atomic values that contains values
such as numbers and strings.

Our first definition concerns the notion of tuple, which formalizes the notion of row or record in
a table:
Tuple: A tuple is a partial function from attribute names to atomic values.

Header: A header is a finite set of attribute names.
Projection: The projection of a tuple t on a finite set of attributes A is

t A a,v : a,v t,a A

The next definition defines relation which formalizes the contents of a table as it is defined in the
relational model.
Relation: A relation is a tuple (H,B) with H, the header, and B, the body, a set of tuples that all
have the domain H.
Such a relation closely corresponds to what is usually called the extension of a predicate in first-
order logic except that here we identify the places in the predicate with attribute names. Usually
in the relational model a database schema is said to consist of a set of relation names, the headers
that are associated with these names and the constraints that should hold for every instance of
the database schema.

LOVELY PROFESSIONAL UNIVERSITY 19