Week 7

• Databases


  A collection of data that is organized and stored in a way that allows for efficient retrieval, management, and manipulation of information
• Physical Database Design


  • Translates logical database design into technical specifications for storing and retrieving data, ensuring database integrity, security, and recoverability
  • Requires knowledge of the specific DBMS that will be used to implement the database (in this case, MySQL)
• Steps in turning a Data Model into a Physical Database Design


  1. Create a table for each entity

  2. Specify a Primary key

  3. Specify attribute properties (data type, null status, default values, data constraints)
• The relation is then analysed and arranged using the normalisation rules
• Physical Database Design


  • Entity Names
  • Entity Relationships
  • Attributes
  • Primary Keys
  • Foreign Keys
  • Table Names
  • Column Names
  • Column Data Types
• Comparing Physical and Logical Schemas, the Physical level includes all the features listed, while the Logical level includes Primary Keys, Foreign Keys, and Naming Constraints
• Constraints


  Rules not to be violated by database users
• Naming Constraints in Physical Database Design

  • Constraints are specific to the DBMS that will host the database (in this case, MySQL)
  • Constraints include removing spaces, avoiding reserved words, and adopting standard naming conventions
• MySQL Database Naming Conventions


  • Remove spaces
  • Avoid reserved words
  • Adopt standard naming conventions
• MySQL Data Types


  • Numeric Data Types
  • Date and Time Data Types
  • String Data Types
• A database contains data, its structure, and Metadata
• Metadata


  Data describing the structure of the data in the database, including number of records, data types, size, description, default values, and rules of use
• When a database is being designed, a data dictionary is created to hold the metadata

  
• Example of a simple data dictionary

  • Table: doggo
  • Field: id, Type: INT, Index: Y, Nullable: N, Other: PRIMARY KEY, AUTO INCREMENT
  • Field: Name, Type: VARCHAR(255), Index: N, Nullable: N
  • Field: Breed, Type: VARCHAR(255), Index: N, Nullable: N
  • Field: Birth, Type: DATE, Index: N, Nullable: N
  • Field: Floof, Type: BOOLEAN, Index: N, Nullable: Y
  • Field: Hooman, Type: VARCHAR(32), Index: N, Nullable: Y

  
• Data Dictionary


  The de-facto representation of the physical design for the database, containing tables, fields, types, additional information, and other details

  
• When updating the database, the data dictionary must be updated first

  
• The data dictionary template for QUB contains important parameters such as Table Name, Database Name, Composite Fields, Foreign Keys, General Description, Primary Key specification, Field Names, Uniqueness, Description, and Typical Data

  
• For the BMC Pharmacy example, 4 data dictionaries are needed, one per entity
• The 4 entities for the BMC Pharmacy example are: PATIENT, GP, TREATMENT, and MEDICATION
• The BMC Pharmacy database is in 3rd Normal Form (3NF)
• Table Name


  The name of the database table

  
• Database Name


  The name of the database
• Composite Field


  • A field that is part of a composite key, made up of multiple fields that together uniquely identify a record
• Foreign Key


  A field in one table that references the primary key of another table, allowing the tables to be linked
• General Description
• Primary Key


  A field or set of fields that uniquely identifies each record in a table
• Field Name


  The name of a column or field in a database table
• Unique


  A field that must contain a unique value for each record
• Typical Data
• Composite Key


  A primary key made up of multiple fields that together uniquely identify a record