1 / 24

Relational Model

Relational Model. Contents:. What is relational model What is the use of relational model 2. Relational model 1. Structural part; 2. Integrity part; 3. Manipulative part (Next Week). 2.1 Relational Model. A relational model includes three parts:.

whitney-osborn
Download Presentation

Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Model Contents: • What is relational model • What is the use of relational model • 2. Relational model • 1. Structural part; • 2. Integrity part; • 3. Manipulative part (Next Week)

  2. 2.1 Relational Model • A relational model includes three parts: a) Structural part, a collection of the type of structures; b) Manipulative part, operations used on databases; c) Integrity part, rules that databases must obey.

  3. 2.2 The Use • It is basis for designing relational RDBMSs. 1) The designer to reason about the database systems without actually having build them; 2) An RDBMS implements the structures, operations and rules of the relational model.

  4. Book_Title Authors Published date Price Relational Databases BEA 2000.5 12.35 Java EFH 2001.2 16.50 2.3 The structural part • Component of the structural part: • Domains • Attributes; • Relations; • Keys. Relational database: is made up of relations. Relation: is a structure describing the relationships between things in the real world.

  5. Book_Title Authors Published date Price Relational Databases BEA 2000.5 12.35 Java EFH 2001.2 16.50 2.3 The structural part The table – relations; The columns – attributes;The column headings – attribute names; The values in the column – attribute values. The rows -- tuples. Table::Relation Column::attribute ?? Row::Tuple

  6. Prod_No Name Colour P1 Pantaloons Blue P3 Socks Harebell P4 Socks White Warehouse_No Location WH1 Leeds WH2 Bradford Warehouse_No Prod_No Quantity WH1 P1 5000 WH1 P3 10000 WH1 P4 10000 WH2 P3 20000 WH2 P4 5000 2.3 The Structural Part • Example: to understand the concepts Product Stock Warehouse What can you see From these tables?

  7. 2.3 The Structural Part • Concepts: Relations, attributes and tuples • A relational database -- a number of relations (tables). • A relation is made up of tuples (rows) each of which is a set of domain values. • An attribute is given a name, • Each attribute must take a value from the domain. • ---- what is domain?

  8. Prod_No Name Colour P1 Pantaloons Blue P3 Socks Harebell P4 Socks White 2.3 The Structural Part • Domain: an attribute in a relational database is defined on a domain. Domain of Prod_No: {P1, P2, P3} Domain of Name: {Pantaloons, Socks} • A domain is a set of values from which the values of one or more attributes are taken. • Every value in a database must be a member of some domain; • Domain values must be single-valued. • Examples 2.2-2.8.

  9. Prod_No Name Colour P1 Pantaloons Blue P3 Socks Harebell P4 Socks White 2.3 The Structural Part Concept: Key HOW to distinguish each PRODUCT? Need to define a Key?

  10. Prod_No Name Colour P1 Pantaloons Blue P3 Socks Harebell P4 Socks White Warehouse_No Location WH1 Leeds WH2 Bradford Warehouse_No Prod_No Quantity WH1 P1 5000 WH1 P3 10000 WH1 P4 10000 WH2 P3 20000 WH2 P4 5000 2.3 The Structural Part • Example: the keys Product Stock Warehouse • Keys: • Product: Prod_No. • Warehouse: Warehouse_No, or Location. • Stock: Warehouse_NO and Prod_No.

  11. 2.3 The Structural Part Concept: Key • Keys are used to represent the names of the things. • A key is one or more attributes of a relation. • The value of a key uniquely identify each tuple.

  12. Prod_No Name Colour P1 Pantaloons Blue P3 Socks Harebell P4 Socks White Warehouse_No Location WH1 Leeds WH2 Bradford Warehouse_No Prod_No Quantity WH1 P1 5000 WH1 P3 10000 WH1 P4 10000 WH2 P3 20000 WH2 P4 5000 2.3 The Structural Part • Note 1 the key for STOCK is made up of two attributes, but any one of them is not key. • Super-key: key + other attributes, e.g., Warehouse_NO+Prod_No, + Quantity.

  13. Warehouse_No Location WH1 Leeds WH2 Bradford 2.3 The Structural Part • Note 2 A relation may have many keys--called candidate keys. • Every relation has at least one candidate key. • One candidate key is selected as the primary key. • The candidate keys not selected as the primary key are called alternatekeys. • Keys: • Warehouse: Warehouse_No, or Location.

  14. 2.3 The Structural Part • A primary key is selected so that we can make sure that each tuple has some unique identifier. • The attributes of primary key are not allowed to have no value at all. • A foreign key: attributes whose values are used elsewhere as primary key values. • Prod_No: a primary key in Product, • A foreign key in Stock. • The primary key and foreign key are defined on the same domain but not necessary have the same attribute names.

  15. Relational Structure Table Files Relation Table File Tuple Row Record Attribute Column Field Attribute name Column heading Field name Attribute value Table entry Data items 2.3 The Structural Part • Table 2.1: summary- relation

  16. 2.3 The Structural Part • Summary – relational model: • Database. A relational database comprises a set of table-like structures called relations. • Relation. A relation is like a table of values, in which no two rows can be identical. • Attributes. A relation is defined on a number of attributes. Each attribute has one name and the attribute value represent facts. • Domain. A domain is a set of values that represent all the facts. Each attribute takes values from a particular domain.

  17. 2.3 The Structural Part • Summary – relational model • Tuple. A relation is a set of tuple. Each tuple is a set of attribute values, one for each attribute of the relation.. A tuple represents a proposition. • Key. A key is a set of attributes whose values uniquely name each tuple of relation. • Super-key is a key plus some other attributes. • candidate keys, primary key, alternate keys, foreign keys.

  18. 2.4 The integrity part • What is the integrity part A set of rules that the Relational Database must obey.

  19. 2.4 The integrity part • What are the rules Only two general rules: • Entity integrity rules • Referential rules

  20. 2.4 The integrity part • Entity Integrity Rules Each tuple must include a unique name or identifier. i.e. The primary key must have a non-null value.

  21. 2.4 The integrity part • Referential Integrity Rules All cross-referenced objects must be within the database. Error!! If you have added the sales order of Customer C9, but forget adding record of C9!

  22. 2.4 The integrity part • What referential integrity rules is? The foreign key value must be either NULL or a value that occurs elsewhere as A PRIMARY KEY.

  23. Order_No Date Cust_No Cust_No Name Address 011 1/7/89 C45 C45 Boys Newsome 012 1/8/89 Null C46 Girls Holey 013 2/5/88 C47 Sales_Order null 1/1/89 C45 Customer 2.4 The integrity part • Example Anything wrong in this database?

  24. 2.4 The integrity part • Summary • Integrity part includes two rules: • Entity rule; • Integrity rule; • They ensure, respectively, • All data objects have names • No object cross-refer a non-existent data object.

More Related