What is a database schema?
A database schema is a data model that represents the structure of a database in terms of its tables, attributes and relationships.
When designing and coding a web application, it is importatnt to keep a correct and up-to-date database schema handy as it is esssential to the correct design and implimentation of the application.
Table Summary
- Tables are used to store something of interest to our application. (If we have a shop, we would ceate seperate tables to store “objects”. We could have a customer table, item table and order table.)
- An attribute is a characteristic of the “objects” we store in our tables. (A customer will have a first name attribute, last name attribute.) Attribute correspond to columns in our tables.
- New rows in our table correspond to a new instance of our “object”. If our customer table has 3 rows (excluding the headers row) then we have 3 seperate instances of the customer “object”.
- Tables should be named using singular nouns, instead of plural, as a standard. (Customer table instead of customers)
What is a table?
A table is a two dimensional data structure that contains one or more rows and columns.
A table is used to store the of something of interest to the application.
Tables should be named with a singular noun or phrase as a standard. (Customer instead of customers)
What is an attribute? How can we identify one in a table?
An attribute is stores a characteristic of somethingof interest to our application.
Each column in our table will be an attribute.
What is a database relationship? How is a relationship implimented?
A relationship is the logical association between two database tables.
Relationships are implimented via primary and foreign keys. These will be shared attributes between two tables. (A customer may have attributes FirstName, LastName and OrderNumber (for orders they place). The FirstName and LastName attributes will be specific to the customer object, however the OrderNumber will also be an attribute of the order object. Through the shared attribute the two tables are linked.)
What is a primary key? What are the two rules when creating primary keys?
A primary key is the attribute (or combination of attributes) in a table that uniquely identifies each row in the table.
- A primary key value must be unique.
- A primary key value must not be null.
Structured Query Language (SQL) Summary
- SQL is a fourth-generation (or imperative) programming language that retrieves and manages the data in a relational database.
- SQL is by far the most widely used relational database language.
The statements of SQL are divided into four categories:
- Data Definition Language (DDL statements)
- Data Control Language (DCL statements)
- Data Query Language (DQL statements)
- Data Manipulation Language (DML statements)
