Relational database concepts
Download
1 / 33

Relational Database Concepts - PowerPoint PPT Presentation


  • 174 Views
  • Updated On :

Relational Database Concepts. Let’s start with a simple example of a database application. Assume that you want to keep track of your clients’ names, addresses, and phone numbers. Name Address Phone Number. Client information to store and retrieve. Name. Address. Phone. Janice Chen.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Database Concepts' - karlyn


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Let s start with a simple example of a database application l.jpg
Let’s start with a simple exampleof a database application

  • Assume that you want to keep track of your clients’ names, addresses, and phone numbers.


Client information to store and retrieve l.jpg

Name

Address

Phone Number

Client information to store and retrieve


A typical design and sample table entries might be l.jpg

Name

Address

Phone

Janice Chen

236 Boylston Ave. Boston, MA 02117

617-331-6235

Al Brown

Bradford St., Provincetown 28, MA 02960

508-478-2654

Janice Chen

36 Commonwealth Ave., Brookline, MA 02113

617-229-7640

A typical design and sample table entries might be:


Slide5 l.jpg


Slide6 l.jpg


Primary key l.jpg
Primary key from MA, or all clients from Brookline, or group clients by ZIP code for mass mailings.

  • How can we identify a record in this table uniquely? Notice that the last name is not sufficient; nor is the combination of last name and first name.

  • Perhaps the combination of all the name and address fields is sufficient.


Slide9 l.jpg

  • A from MA, or all clients from Brookline, or group clients by ZIP code for mass mailings.primary key in a table is a field or combination of fields that uniquely distinguishes any record from all other records.

  • Every table in a relational database must have a primary key.


Slide10 l.jpg


Slide11 l.jpg

  • A single field is generally preferable as a primary key, so one choice we have available for our database is to add an additional field – Social Security Number. These are guaranteed to identify individuals uniquely.

  • An alternative is to generate our own numbering system for clients, say C001, C002, etc.


Now let s consider a slightly more involved example l.jpg
Now let’s consider a slightly more involved example one choice we have available for our database is to add an additional field – Social Security Number. These are guaranteed to identify individuals uniquely.

  • Assume that you have a small company selling books and you want to keep track of your customers’ orders.

  • To keep things simple, let’s assume that among the data we intend to keep track of for each order is the following:


Data to store and retrieve about customer orders l.jpg

Customer Name one choice we have available for our database is to add an additional field – Social Security Number. These are guaranteed to identify individuals uniquely.

Customer Address

Book Title

Quantity Ordered

Date Shipped

Purchase Price

etc.

Data to store and retrieve about customer orders


Slide14 l.jpg


Slide15 l.jpg


Slide16 l.jpg


Slide17 l.jpg

  • The solution is to have multiple tables. which can lead to more date entry errors or updating errors if some of the customer data changes. And of course it wastes space.

  • In particular, we should have a separate Customers table that holds only things like the name and address of customers.

  • We’ll also have an Orders table.


Foreign keys l.jpg
Foreign keys which can lead to more date entry errors or updating errors if some of the customer data changes. And of course it wastes space.

  • One of the fields in the Orders table will identify which customer the order is for.

  • Instead of using the entire name and address of the customer in the Orders table, we’ll use a simple customer id number to identify the customer.


Slide19 l.jpg


Many to one relationships l.jpg
Many-to-one relationships Customers table, and it will also be used as a

  • Since each order is placed by exactly one customer, but each customer may place many orders, there is a many-to-one relationship between orders and customers.

  • Many-to-one relationships always require the use of a foreign key in the “many” table (Orders in this case).


Slide21 l.jpg


Many to many relationships l.jpg
Many-to-many relationships keep track of our book inventory.

  • One order can be for more than one book, and any book can appear in multiple orders.

  • Thus there is a many-to-many relationship between books and orders.


Slide23 l.jpg


Slide24 l.jpg


Slide25 l.jpg

  • Here is how the Relationships Window in Access displays this information, by listing the names of all the fields in all 4 tables and showing which fields (foreign keys) correspond to which other fields(primary keys) in other tables. Primary keys are indicated in bold. (ISBN number is a standard unique identifier for books.)


Referential integrity l.jpg
Referential Integrity information, by listing the names of all the fields in all 4 tables and showing which fields (foreign keys) correspond to which other fields(primary keys) in other tables. Primary keys are indicated in bold. (ISBN number is a standard unique identifier for books.)

  • The point of this example has been to show how a useful database may contain multiple tables.

  • But when there are related tables, care must be taken to insure that the relationships between the tables are respected.


Slide28 l.jpg


Slide29 l.jpg

  • There are 2 possible solutions to this problem: Customers table.

    • Do not allow a customer to be deleted from the Customers table if there are corresponding orders in the Orders table

    • When deleting an entry from the Customers table, delete all corresponding orders for that customer (called cascaded deletion).


Queries and joins l.jpg
Queries and Joins Customers table.

  • Retrieving information from the database is done using queries.

  • Consider a query of the form: Find the names and addresses of all customers who have ordered books by a particular author.


Slide31 l.jpg

  • This involves: Customers table.

    • looking in the Books table to find all books by that author,

    • then looking in the Order Details table to see which orders were for those books,

    • then looking in the Orders table to see which customers placed those orders,

    • and finally, looking in the Customers table to get the names and addresses of these customers.


Slide32 l.jpg


Slide33 l.jpg

  • A query involving multiple tables is sometimes said to be a query on the join of those tables.

  • The actual join itself need not be determined; it is just a conceptual entity.

  • Just think of the process in the way we described it earlier: looking up information in one table, then in another table, etc.


ad