Download
1 / 23
230 likes | 353 Views
Database Design. CIS 218. Three Stages of Database Development. Requirements Design Implementation. The Design Stage. Identify Entities Identify Attributes Choose Primary Keys Normalization Identify Relationships. Entity. A Person, Place, Thing or Transaction
E N D
Database Design CIS 218
Three Stages ofDatabase Development • Requirements • Design • Implementation
The Design Stage • Identify Entities • Identify Attributes • Choose Primary Keys • Normalization • Identify Relationships
Entity • A Person, Place, Thing or Transaction • Something the user wants to track
Entities • At HCC, what are the things we might want to track? • An entity is represented as a table
Subject Author Title ??? Number of Pages Attributes • An Attribute is a property that describes an entity • A Book has a(n)…
Attributes • What are the attributes of a Car?
Attributes • Attributes describe an entity’s characteristics • Employee(ssn, first, last, jobTitle) • Student(sid, name, gpa, email, phone) • Flight(flightNum, date, time, origin, destination) • Book(isbn, title, author, genre) • Represented as columns in a table entity attributes
Practice • Identify three entities in the list below. • Identify three attributes that belong to each entity. Breed Size Shirt CPU Speed Manufacturer Serial Number Style Weight Color Animal Gender Computer
What are the entitites? What are the attributes of each entity?
What are the entitites? What are the attributes of each entity?
What are the entitites? What are the attributes of each entity?
Primary Key • A unique identifier • No two rows in a table may be identical
Primary Keys Student sid ssn name phone email • Criteria • Short • Numeric • Stable • Not private • Surrogate Key • If no unique identifier exists, a surrogate key is used as the primary key, usually an AutoNumber • Usually hidden in forms, reports, and queries • Composite Key • A primary key consisting of more than one column
Practice • What would make the best primary key for each entity? • Employee(ssn, first, last, jobTitle) • Flight(flightNum, date, time, origin, destination) • Member(firstName, lastName, dateJoined, status) • Grade(sid, yrq, itemNum, grade)
Be Normal • A table that is “well-formed” is normalized • The Golden Rule of Being Normal If attribute X can have more than one attribute Y, then they don’t belong in the same table!
A Course can have more than one Book! Course CourseID CourseName Textbook1 Textbook2 Course CourseID CourseName Textbook ISBN Title Course CourseID CourseName Textbook Title
An Advisor can have more than one Student! Student sid name gender advisorName extension Student sid name gender Advisor name extension Student sid name gender Advisor advisorID name extension
A Pet can have more than one Treatment! Pet petID name treatment price Treatment treatmentID treatmentName price Pet petID name Treatment treatmentName price Pet petID name
A Pet can have more than one Breed! Pet petID name breed Breed breedID breedName Pet petID name breed Pet petID name breed Breed breedName
A Breed can have more than one Pet! Pet petID name breed minWeight maxWeight avgLifeExpectancy Breed breed min_weight max_weight avg_life_expectancy Breed breedID breed minWeight maxWeight avgLifeExpectancy Pet petID name Pet petID name
Denormalization • Complexity vs. modification problems • Normalizing relations may significantly increase the complexity of the data structure • Denormalized relations may be preferred • CUSTOMER and ZIP • It is inefficient to read from two different tables to get a customer’s address • Modification problems are infrequent, because zip codes rarely change • Deletion problems are not usually an issue
