database design n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Database Design PowerPoint Presentation
Download Presentation
Database Design

Loading in 2 Seconds...

play fullscreen
1 / 14

Database Design - PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on

Database Design. Dr. M.E. Fayad, Professor Computer Engineering Department, Room #283I College of Engineering San José State University One Washington Square San José, CA 95192-0180 http://www.engr.sjsu.edu/~fayad. Lesson 08-2: Misc. Topics in DB. 2. Lesson Objectives.

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 'Database Design' - ursula


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
database design

Database Design

Dr. M.E. Fayad, Professor

Computer Engineering Department, Room #283I

College of Engineering

San José State University

One Washington Square

San José, CA 95192-0180

http://www.engr.sjsu.edu/~fayad

SJSU -- CmpE

slide2

Lesson 08-2:

Misc. Topics in DB

2

SJSU – CmpE --- M.E. Fayad

slide3

Lesson Objectives

  • Understand DB Terminology
  • Explain Relation Properties
  • Understand how to Identify Candidate,

Primary, Alternate, and Foreign Keys

  • Understand Entity Integrity and Referential Integrity

3

SJSU – CmpE --- M.E. Fayad

terminology ansi sparc database architecture
External Level

View Level -- Users’ views of the db.

Conceptual Level

Logical View & Constraints View

Internal Level

Physical View or Computer’s view of the db.

Terminology: ANSI-SPARC Database Architecture

4

SJSU – CmpE --- M.E. Fayad

more terminology
A Relation is a table with columns & Rows.

An attribute is a named column of a relation.

A domain is the set of allowable values for one or more attributes.

A tuple is a row of a relation.

The degree of a relation is the number of attributes it contains.

More Terminology

5

SJSU – CmpE --- M.E. Fayad

example

branchNo street city postCode

B005 22 Deer Rd London SW1 4EH

B007 22 Post St Aberdeen AB2 3SU

B003 22 S. Main St Glasgow GI1 9QX

B004 22 Mo Ave Bristol BS9 1NX

B002 56 Clover Dr London NW5 6EU

staffNo

branchNo

B005

B007

B003

B004

B005

Branch

Example

Staff

Primary Key

Foreign Key

6

SJSU – CmpE --- M.E. Fayad

more terminology1
A Relation Schema – A named relation defined by a set of attribute and domain name pairs where:

R (A1:d1, A2:d2, …., An:dn)

A Database Schema – A set of relation schema each with a distinct name.

R = (R1, R2, ….., Rn)

More Terminology

7

SJSU – CmpE --- M.E. Fayad

properties of relations 1
A relation has a name that is distinct from all other relation names within the same db.

Each cell of the relation contains exactly one atomic (single) value.

Each attribute has a distinct name.

The values of an attribute are all from the same domain.

Properties of Relations (1)

8

SJSU – CmpE --- M.E. Fayad

properties of relations 2
Each tuple is distinct, there are no duplicate tuples.

The order of attributes has no significance.

The order of tuples has no significance,

Properties of Relations (2)

9

SJSU – CmpE --- M.E. Fayad

relational keys 1
Superkey – An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

Candidate key – A Superkey, such that no proper subset is superkey within the relation.

A candidate key, k, for a relation R has two properties:

Uniqueness: In each tuple of R, the value of k uniquely identify the tuple.

Irreducibility – no proper subset of k has the uniqueness property

Relational Keys (1)

10

SJSU – CmpE --- M.E. Fayad

relational keys 2
They may be several candidate keys for a relation.

Composite key – A key consists of more than one attribute.

Exampes:

Do you think “city” in Branch relation is a candidate key? The answer is no. Why? Because London has two branch offices, therefore, the attribute “city” cannot be a candidate key.

Is “branchNo” a candidate key? Yes, Why? By Examining “branchNo”, each branch office has a unique branchNo,

Is postcode is a candidate key? Yes.. Why?

Relational Keys (2)

11

SJSU – CmpE --- M.E. Fayad

relational keys 3
Primary key: The candidate key that is selected to identify tuples uniquely within the relation

Alternate keys – The Candidate keys that are not selected to be the primary key.

Exampes:

In the Branch Relation, we can choose branchNo as the primary key and

Postcode as an alternate key.

Relational Keys (3)

12

SJSU – CmpE --- M.E. Fayad

relational keys 4
Foreign key: An attribute, or a set of attributes, with one relation that matches the candidate key of some (possibly the same) relation.

branchNo appeared in two relations: Branch and Saff relations

branchNo is a primary key in Branch relation and it is a foreign key in the Staff relation.

Relational Keys (4)

13

SJSU – CmpE --- M.E. Fayad

discussion questions
T/F

Each cell of the relation contains exactly on atomic (single) value.

Primary key is the candidate key that is selected to identify tuples uniquely within the database.

Foreign key is an attribute or a set of attributes within on relation that matches the candidate key of some relation.

The degree of a relation is the number of tuples it contains.

Discussion Questions

14

SJSU – CmpE --- M.E. Fayad