database elements. rhs – 2009 2 database elements a database can be compared to an old- fashioned...
Post on 01-Apr-2015
216 Views
Preview:
TRANSCRIPT
Database elements
RHS – 2009 2
Database elements
• A database can be compared to an old-fashioned file cabinet– The entire canibet is the
database– A drawer is a table– A folder is a record– One piece of information
is a field
RHS – 2009 3
Table
• A table contains data about one specific type of entity, and always on the same form
Person
Name Address Phone Born Married
Per Jensen Skolevej 12 31458791 17-07-1962 No
Jane Høst Allégade 70 52442109 31-03-1971 Yes
Allan Ravn Svinget 49 44553177 12-12-1977 Yes
Ole Olsen Junivej 112 41764430 26-04-1950 Yes
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 No
RHS – 2009 4
Table
• A table should have a proper name, reflecting the type of entity stored in the table
• Some tables are directly related to entities, others are related to relations between entities
• A table definition consists of definitions of data fields, each with a name and type
RHS – 2009 5
Record
• A record is a single row in a specific table, containing information about a single entity
Person
Name Address Phone Born Married
Per Jensen Skolevej 12 31458791 17-07-1962 No
Jane Høst Allégade 70 52442109 31-03-1971 Yes
Allan Ravn Svinget 49 44553177 12-12-1977 Yes
Ole Olsen Junivej 112 41764430 26-04-1950 Yes
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 No
RHS – 2009 6
Record
• All records in a specific table always has the same form
• All records in a specific table must be unique (have a key field)
• A key field consists of one or more fields in the table definition, the combination of which is always unique
RHS – 2009 7
Record
• What makes a Person unique…?
PersonName Address Phone Born Married
Per Jensen Skolevej 12 31458791 17-07-1962 No
Jane Høst Allégade 70 52442109 31-03-1971 Yes
Allan Ravn Svinget 49 44553177 12-12-1977 Yes
Ole Olsen Junivej 112 41764430 26-04-1950 Yes
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 No
RHS – 2009 8
Field
• A field is a single piece of information, about one specific entity in the table
Person
Name Address Phone Born Married
Per Jensen Skolevej 12 31458791 17-07-1962 No
Jane Høst Allégade 70 52442109 31-03-1971 Yes
Allan Ravn Svinget 49 44553177 12-12-1977 Yes
Ole Olsen Junivej 112 41764430 26-04-1950 Yes
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 No
RHS – 2009 9
Field
• A field has a name and a type
• The type of the field should reflect the possible content of the field– String (text data)– Number (numeric data)– Date (dates, time)– Yes/No (binary choices)– Binary (picture, attached file,…)
RHS – 2009 10
Designing a database
• Designing a database looks fairly easy…
• Designing it correctly can be more challenging!
• Correctly: Avoid the two arch enemies of databases– Redundancy– Inconsistency
RHS – 2009 11
Designing a database
• Redundancy: the phenomenon that the same data occurs multiple times in the database– Makes the database unnecessarily large– Data processing becomes slower– Increases the risk of inconsistency
RHS – 2009 12
Designing a database
Name Address Phone Born CoursePer Jensen Skolevej 12 31458791 17-07-1962 English
Per Jensen Skolevej 12 31458791 17-07-1962 IT
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 English
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 Economics
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 Math
Student
RHS – 2009 13
Designing a database
Id Name Address Phone Born
1 Per Jensen Skolevej 12 31458791 17-07-1962
2 Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972
Student
Enrollment Id Course
1 English
1 IT
2 English
2 Economics
2 Math
RHS – 2009 14
Designing a database
• Inconsistency: the phenomenon, that some data is in conflict with each other. Not all data can be correct
• Which data should we trust then?
• If the database is free of redundancy, simple inconsistency cannot occur
RHS – 2009 15
Designing a database
Name Address Phone Born CoursePer Jensen Skolevej 12 31458791 17-07-1962 English
Per Jensen Skolevej 21 31458791 17-07-1962 IT
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 English
Ditte Dorfmann Dalstrøget 3 43519982 11-08-1972 Economics
Ditte Dorfmann Bakkehøj 22 33128769 28-03-1976 Math
Student
RHS – 2009 16
Designing a database
Exercise1. We wish to construct a database for data about our friends. More
specifically, we would like to store: Name, Address, Zipcode, Phone, E-mail, Marital status, date of last contact, and a picture (feel free to include more data if you wish)
2. A single table called Friend might be enough. Design such a table, including definitions of each data field needed in the table. Remember that the table should contain a key field
3. Look at your final table definition. Is there any danger of redundancy and/or inconsistency?
4. (If yes in 3.) Try to think of a better design, to eliminate the risk of redundancy and/or inconsistency? (Tip: you may need to include more tables in the database)
top related