Transcript Databases

Databases and modelling
1. day
Agenda
• Goals
• Plan
• What is database?
• How is database developed and
designed?
• Database design
2
Databases and modelling
• Goal: That you can model and implement a small
database
• Form: Mix of lectures and exercises
• Design task:Construction of a litterature database
3
Formalities
The module is concluded by an internal written individual test
in: IT technology: Databases. The test takes the form of a set
take-home assignment to be handed in after 3 days, in which
the student completes a designated task on data modelling,
SQL, and modelling and search tools, thus documenting his/her
command of the techniques covered in the course. A grade
according to the 7-point grading scale is given.
In relation to the competence goals stipulated in section 8 of
the study regulation the test documents the student’s ability to:
•choose the form of and execute data modelling on a limited
set of data
•define suitable search functions for the above
•reflect on own practice and document the reflection.
4
Course plan – this week
• Monday:10.15-12: Introduction to
databases – data analysis and modelling
• Tuesday 9-12: Design of database tables
and relations
• Wednesday 9-12: Design of database user
interface, design of queries and forms,
introduction to SQL
• Thursday: Database implementation
(without teacher)
• Friday: Database implementation (without
teacher)
5
Course plan – next week
• Monday: 9-12 Introduction to CMS and
portfolio, Typo3 basics
• Tuesday: 9-12 Typo3 basics
• Wednesday: Portfolio implementation
continued and reflection (without teacher).
• Thursday: Portfolio implementation
continued and reflection (without teacher).
• Friday: 9-11 Presentation of portfolio,
database and reflections week 36-37
6
What is a database?
A database is a collection of logically related data
designed to meet the information needs of one or
more users.
Wikipedia
7
What is a database?
• Hierarchical databases –
e.g. windows file system
• Relational databases –
e.g. MS Access, the focus
of this course
8
Relational databases
• User Interface – data in & out
(Reports)
(Forms and queries)
• Data: Tables and relations
9
Methodology
• Start with the goal – what
do you want?
• What do you want to be
able to find in the
database?
• What must the database
as a consequense of the
above contain?
10
Step 1-2
11
For now…
• Just be creative – there is so many ways to
find and express the goal of a database and
some of them are covered by the other
courses.
• Use 5-10 minutes to decide what you want
to be your goal in designing a database for
data about the stuff you read and learn.
• On to data analysis and design…
12
Step 3
13
Example
14
Data design
Use 10-15 minutes to start
designing your data
 To begin with, list the data you
want to find in the database
 Distinguish between entities
(usually nouns in a narrative
about the system), their
attributes and relations (look
for verbs in the narrative)
15
Step 4
16
Table design
• From data to formal description of tables and
relations
• The database stores data in tables with relations to
each other
• 3 normal forms
• The principles for normal forms and normalization
explains the need for relations – but said in a
simpler way the database is split in more tables to:
 Make the database smaller
 Make data input easier
 Make search easier
17
1. Normal form
• All table entries must be of equal
length.
• The table must not contain repeating
entries or groups
• Example: In a litterature database the
author must be placed in an individual table
– otherwise entries would be of different
length depending on number of authors.
18
2. Normal form
• Give all tables a primary key (tables
created to handle relations can have a
primary key).
• Make separate tables for each attribute
shared between many entities.
19
3. normalform
• Entries in a table must not be
functionally dependent of each
other – only of the primary key.
• Example: A database does not have
employe ID and name in the same
table – unless one of them is primary
key.
20
Entity-relation diagram
Entity
Attribute
Relationship
Cardinality – what kind of relation?
•One to one
•One to many
•Many to many
•Many to one
21
Design database
• Use an entity-relation diagram to
document the table design you want
to implement
• Browse chapter one
• Browse the next chapters about
Access.
22