Before you create a physical database design what must you have
- Which DBMS software to use
- Logical database design (minimally in 3NF)
- Transaction analyses
What is step 1.1 of design base relations
- Defining tables and columns (choosing data types, lengths etc)
- Defining primary keys
What should be considered when choosing if a derived attribute should be stored or calculated each time
if the derived attribute is very frequently queried it may be better to store it rather than calculating each time
How can we represent derived data (4 options)
- Use a generated column
- create a view
- use triggers
- calculate it each time
How is a data base stored
as a collection of files. a database file is partitioned into fixed-length storage units called blocks (or pages)
Define a block/Page
a contiguous sequence of sectors from a single track tend to be 16KB each block
What is primary storage
Volatile storage. E.g., main memory, cache memory
Where operation on data happens
What is secondary storage
Non-volatile storage. E.g., magnetic disk, optical disk, tape, flash drives,…
Where the entire DB is stored
What is stable storage
Replicates information in several non-volatile storage media with independent failure modes. E.g., RAID (Redundant Array of Independent Disks)
Where the DB is backed up
What is a heap file
unordered collection of records placed on a disk in no particular order. fast for insert
What is a sequential file
records are ordered by the value of a specific attribute.
What is a hash file
Records are placed on disk according to a hash function
Which objects physically store data: tables or views?
Tables store data; views do not
How are rows stored on the disk in a clustered index
the rows are stored physically on the disk in the same order as the index
How are rows stored on the disk in a Non-clustered index
an index which maintains a logical ordering of data rows without altering the physical ordering of the rows.
What is the denormalisation process
is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.
What is DDL used for
to create tables
What is DML used for
to insert and manipulate data
What is physical database design
Decide how the DB is stored on secondary storage.
Includes:
Base relations (tables)
File organization (how blocks/pages are arranged)
Indexes
Constraints & security
What are the steps of physical database design
1.1 Design base relations
1.2 Represent derived data
1.3 Enforce general constraints
2.1 File Organisations
2.2 Choose Indexes
3. create views for users/applications
4. security measures (authentication etc)
5. Denomalisation - controlled redundancy
6. storage structure overview
