dat602 database application development lecture 4 database design
TRANSCRIPT
![Page 1: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/1.jpg)
DAT602 Database Application Development
Lecture 4 Database Design
![Page 2: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/2.jpg)
Database Application Development - Lecture 4
Basic structure of database application• Three ties model
• Database is the foundation of whole system.Generally, we should design database at very beginning.
Database
Application server (Business logic)
Client (User interface)
![Page 3: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/3.jpg)
Database Application Development - Lecture 4
Analyze Requirement• Requirement analysis is complicated.• For database design you should consider
which kinds of data are necessary for the expecting system?
• The more careful you consider it, the less risk of modifying database you’ll take in the future.
![Page 4: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/4.jpg)
Database Application Development - Lecture 4
• In this lecture, we’ll use a Mini online shop as a example to demonstrate how to design a simple database.
• Basic requirement:Sellers can put products online.Buyers can view and buy available products.Details of transaction should be kept.
![Page 5: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/5.jpg)
Database Application Development - Lecture 4
Mini Online shop• How many entities should be involved in this
system?Customers, sellers, products, transaction.
• Create one table for each entity.
Customer Seller Product Transaction
Tables
![Page 6: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/6.jpg)
Database Application Development - Lecture 4
Design a table• Which data is needed for each table?• What types of these data?• How long of data is appropriate?• Which column should be primary key?
![Page 7: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/7.jpg)
Database Application Development - Lecture 4
Table design• Which data is needed for each table?
Only cover necessary and close related data.Column Type
Name Char
ID Char
Email Char
Telephone Char
Height Float
Unnecessary
![Page 8: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/8.jpg)
Database Application Development - Lecture 4
Table design• What types of these data?
Float, integer, char, date, etc..
Different databases have different type systems, but they are similar.
char vs. varcharThe char is a fixed-length character data type, the varchar is a variable-length character data type.
![Page 9: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/9.jpg)
Database Application Development - Lecture 4
Table design• How long of data is appropriate?
8bit, 32bit, 128bit?
Longer data costs more storage. Consider carefully.
1 Chinese character = 2 byte = 16 bit
![Page 10: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/10.jpg)
Database Application Development - Lecture 4
Table design• Which column should be chosen as primary
key?Chose the column will never have two same values.Primary key is used to identify different records.
![Page 11: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/11.jpg)
Database Application Development - Lecture 4
Customer table.
Column Name Type Length(Byte)
UserName char 16
Address char 128
TelephoneNO char 16
UserID char 16
Email char 32
Balance float 16
Primary Key
![Page 12: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/12.jpg)
Database Application Development - Lecture 4
Transaction table
Column Name Data type Length
TransactionID char 16
Date date 16
BuyerID char 16
SellerID char 16
ItemID char 16
Amount float 16
TotalPrice float 16
Primary Key
Foreign Key
Foreign Key
![Page 13: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/13.jpg)
Database Application Development - Lecture 4
Relationship between tables
Column Type
TransactionID char
BuyerID char
SellerID char
… …
Column Type
UserID char
UserName char
… …
Column Type
SellerID char
SellerName char
… …
Primary key
Primary key
Primary key
Foreign key
Foreign key
![Page 14: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/14.jpg)
Database Application Development - Lecture 4
Normalization• Normalization eliminates redundancies and to
ensure consistent dependencies.Simply, normalization refines database design.
![Page 15: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/15.jpg)
Database Application Development - Lecture 4
Normalization guideline• There must be no repeated fields.• The table cannot contain fields that are not
related to the primary key.• For a multiple keys table, the table should only
contain data related to one entity, and that entity should be described by its primary key.
• The table cannot contain two or more independent multivalued facts about an entity.
![Page 16: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/16.jpg)
Database Application Development - Lecture 4
• There must be no repeated fields.
Column Type
UserID Char
FullName Char
Surname Char
… …
Fullname and Surname are conflict, remove Surname or keep Surname and use GivenName to replace Fullname.
Column Type
UserID Char
GivenName Char
Surname Char
… …
Customer tableNormalized Customer table
![Page 17: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/17.jpg)
Database Application Development - Lecture 4
• The table cannot contain fields that are not related to the primary key.
Column Type
UserID Char
UserName Char
TransactionID Char
… …
For customer table, TransactionID is not a part of customer information, but a part of transaction, you should remove it.
Column Type
UserID Char
UserName Char
… …
Customer tableNormalized Customer table
![Page 18: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/18.jpg)
Database Application Development - Lecture 4
• For a multiple keys table, the table should only contain data related to one entity, and that entity should be described by its primary key.
Column Type
TransactionID Char
BuyerID Char
SellerID Char
BuyerName Char
… …
Primary key
Foreign key
Foreign key
Transaction Table
Column Type
TransactionID Char
BuyerID Char
SellerID Char
… …
Normalized Transaction Table
![Page 19: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/19.jpg)
Database Application Development - Lecture 4
• The table cannot contain two or more independent multi-valued facts about an entity.
UserID(Primary Key)
Telephone
007 123456
007 345678
008 389207
… …
Customer TableUserID(Primary Key)
Telephone1 Telephone2
007 123456 345678
008 389207 <NULL>
… …
Normalized Customer Table
Primary key can not has two records with same value.
![Page 20: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/20.jpg)
Database Application Development - Lecture 4
• ViewViews are implemented in a relational database system by allowing the user to select data from the database to create temporary tables, known as views.
![Page 21: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/21.jpg)
Database Application Development - Lecture 4
TransactionID Date UserID UserName UserTelephone Seller ….
Transaction Customer Seller
View of detailed transaction
Tables
![Page 22: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/22.jpg)
Database Application Development - Lecture 4
• Keep each table as simple as possible, just assign necessary columns to it, you can create views by combining data from multiple tables.
![Page 23: DAT602 Database Application Development Lecture 4 Database Design](https://reader036.vdocuments.us/reader036/viewer/2022062517/56649f175503460f94c2e185/html5/thumbnails/23.jpg)
Database Application Development - Lecture 4
• Literature“Java Database Programming Bible”,by John O'Donahue ISBN:0764549243John Wiley & Sons © 2002
Chapter 2