databases

• Database

  An organized collection of structured information or data stored electronically in a computer system
• Common Database Models

  • Hierarchical database model
  • Relational model
  • Network model
  • Object-oriented database model
  • Entity-relationship model
  • Document model
  • Entity-attribute-value model
  • Star schema
  • Object-relational model
• Database Management Systems

  • MySQL
  • PostgreSQL
  • Oracle
  • Microsoft SQL Server
  • Microsoft Access
• Database Model


  Shows the logical structure of a database, including relationships and constraints determining how data can be stored and accessed
• The database management system ensures the integrity of data, shared data among multiple applications, and easy enforcement of standards
• Relational Model


  Sorts data into tables (relations) consisting of columns and rows, where each row represents one instance of the object described in the table
• Column


  The set of facts that are kept track of about the type of object in a table
• Where databases are used

  • Websites: E-Bay, Amazon, Takealot, banks (account holder details & transactions & credit records)
  • Airlines use databases to store and access information about flight schedules and reservations
  • Supermarkets point of sale
  • Forensics
• Database Management System (DBMS)

  An application software that interacts with users and other application programs designed to define, capture, manipulate, retrieve, and manage data in a database
• The main purpose of a database is to operate a large amount of information by storing, retrieving, manipulating, and managing data
• Row


  Also called a record, represents one instance of the type of object described in a table
• What a DBMS can handle

  1. Data Entry And Update: add data, change data, delete data

  2. Queries: ask complex questions about the data in the database

  3. Forms: produce attractive and useful forms for entering, viewing, and updating data

  4. Reports: create sophisticated reports for presenting data
• Attribute


  Characteristic of the entity in question which describes the instances(records) in the column of a database, such as student ID, Firstname, lastname, etc.
• Each row (tuple) includes data about a specific instance of the entity in question, e.g. a particular employee
• Database table


  Contains information about different types of things
• Domain


  Attributes in a relation
• Foreign key


  If a unique attribute (key) is referenced in another table
• Creating a Table


  Options: Select Table on the Tables group of the Create tab to get the default Datasheet table creation view; Alternatively, select Table Design to get the table Design view
• Creating a New Database

  Access the Field entry point in Datasheet View; Start in Datasheet view; Navigation Panel; The Ribbon; Groups of objects displayed; Tick All Access Objects to see all elements of your DB
• Database design

  1. Identify the tables (Main objects)

  2. Determine the fields (Records you want to store for each table/entity in the table)

  3. Determine data types for the fields (What are you storing? How best to represent it?)

  4. Determine the primary keys (Fields that are unique identifiers for each object/table)

  5. Determine relationships among the tables (How do the objects relate to one another in reality?)

  6. Identify and remove redundancy (Avoid repetitions that can cause problems)
• Column


  Set of facts that we keep track of about that type of object; also called an attribute
• GLUE in a relational database

  Foreign Keys (FK)
• Saving a Table

  When you change from table Design view to table Datasheet view, you'll be prompted to save your table and give it a name
• Advantage of relating tables

  It is easy to find data in both because they are related
• Row


  Represents one instance of the type of object described in that table; also called a record
• Primary key

  Attribute or combination of attributes that uniquely identify each table record
• Changing views

  You can switch between Datasheet and Design view at any time by either right-clicking on the table and selecting the view you wish to change to from the context menu or using the view button on the far left of both the Datasheet and Design tabs
• Saving a Table

  When you change from table Design view to table Datasheet view you’ll be prompted to save your table and give it a name
• Relationship Types in relational database design
• Many-to-Many Relationship

  • A row in table A can have many matching rows in table B, and vice versa
• Implementing Relationships in MS Access

  1. In the starting database window, select the Relationships button

  2. To view your table relationships, click Relationships on the Database Tools tab
• Description in Table Design View

  • Is useful as others may have to use the database and ease of maintenance
• Data Types in Table Design View


  • Determine the type of data that should be allowed to be entered in the column
• Database design steps

  1. Identify the tables – Main objects

  2. Determine the fields – Records you want to store for each table/entity in the table

  3. Determine data types for the fields - What are you storing? How best to represent it?

  4. Determine the primary keys - Fields that are unique identifiers for each object/table

  5. Determine relationships among the tables – How do the objects relate to one another in reality?

  6. Identify and remove redundancy - Avoid repetitions that can cause problems
• Relationship Types

  • One-to-One
  • One-to-Many (or Many-to-One)
  • Many-to-Many
• Changing view in a table

  1. Right-clicking on the table and selecting the view you wish to change to from the context menu

  2. Using the view button on the far left of both the Datasheet and Design tabs
• One-to-Many Relationship

  • A row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A
• Table Relationships

  1. In order to create a Query that can extract data from more than one table, the tables must be linked

  2. Prevent duplication and redundancy
• Field Names in Table Design View

  • Become column headings
• Setting up Tables and Naming Fields (Programming Standards)


  1. Create tables with meaningful names

  2. Consider using the underscore character instead of a space in table names

  3. Use only alphanumeric characters when naming tables

  4. Create a primary key on tables

  5. Avoid using reserved names for naming tables or fields

  6. The name can contain up to 31 characters