Download Creating the Data Structure - Buisness Management - Lecture Slides and more Slides Business Administration in PDF only on Docsity!
Next step - creating the data
structure
- Few rules - a lot of experience
- Can get quite complex (paramount for the speed of the DB)
- Tables must be normalised - ie redundancy is limited to the strict minimum by an algorithm
- In practice, normalisation is not always the best
Data Structure Diagrams
- Describe the underlying structure of the DB: the complete logical structure
- Data items are stored in tables linked by pointers
- attribute pointers: data fields in one table that will link it to another (common information)
- logical pointers: specific links that exist between tables
- Tables have a key just like files
Normalisation
- Process of simplifying the relationships amongst data items as much as possible (see example provided - handout)
- Through an iterative process, structure of data is refined to 1NF, 2NF, 3NF etc.
- Reasons for normalisation:
- to simplify retrieval (speed of response)
- to simplify maintenance (updates, deletion, insertions)
- to reduce the need to restructure the data for each new application
First Normal Form
- design record structure so that each record looks the same (same length, no repeating groups)
- repetition within a record means one relation was missed = create new relation
- elements of repeating groups are stored as a separate entity, in a separate table
- normalised records have a fixed length and expanded primary key
Third normal form
- to remove transitive dependencies
- when one item is dependent on an item which is dependent from the key in the file
- relationship is split to avoid data being lost inadvertently
- this will give greater flexibility for the design of the application + eliminate deletion problems
- in practice, 3 NF not used all the time - speed of retrieval can be affected
Creating links between the tables
- use common fields to join tables / queries
- very easy when data is properly normalised
- Gives total flexibility in terms of data retrieval
- Main strength of RDBs (SQL)
Querying RDBs with SQL
- use a form of pseudo english to retrieve data in a view (which looks like a table)
- syntax is based on a number of “clauses”
- Select: specifies what data elements will be included in the view
- From: lists the tables involved
- Where: specifies conditions to filter the data
- specific values sought
- links between tables
Example with one table
- find the name and address of customer number 1217
Select name, address
from [customer table]
where cust. # = 1217
Example with two tables
- find the rep name of all customers
select [customer table].name, [rep table].[rep name]
from [customer table], [rep table]
where [customer table].[rep#] = [rep table].[rep #]
Example with two tables
- same for customer Murphy only
select [customer table].name, [rep table].[rep name]
from [customer table], [rep table]
where [customer table].[rep#] = [rep table].[rep #]
and [customer table].name = “murphy”
