A relational database management system must have database tables that relate in some way
Cardinality refers to the way that one database table relates to another
When planning databases or systems with ER Diagrams, we use cardinality notation to solve relationships
There are three different types of cardinality; one-to-one, one-to-many and many-to-many
We often practice normalisation in order to resolve many-to-many cardinality relationships
One-to-one (1:1)
This occurs when two database tables directly relate to eachother
For example, if we have two tables; Drivers and Cars
In our database, only one driver may have one car at any time
Hence the relationship remains 1 (driver) : (drives) : 1 (car)
see here for why have a 1:1 relationship setup for security reasons
these are not very common
One-to-many (1:M)
This occurs when a table relates to many other tables in a database
For example, if we have two tables; Students and Assignments
In our database, one student can have submitted many assignments, but each one assignment is submitted by one student
Hence, the relationship remains 1 (student) : (submits) : M (assignments)
these are most common in an RDBMS
Many-to-many (M:N)
This occurs when many tables relate to many other tables in a database
This is virtually unachievable, hence why we use the process of normalisation to resolve many-to-many relationships
Read more about Normalisation by The Computing Teacher here
SQL stands for Structured Query Language
It's considered one of the most common languages used to communicate with databases
SQL is how we can input and retrieve information from a datbase, as well as update, edit and even remove
A general SQL query will follow the structure: SELECT [x] FROM [y] WHERE [z]
Where [x] is a database, [y] is a table and [z] is the condition
SQL is heavily English-based which makes it easy to understand and work with
"SELECT" begins our SQL query
It tells the database what information we wish to view from the proceeding query
Examples of information to select are: "* (Wildcard; all"), "FirstName", "LastName", "Gender"
"FROM" tells the database where to look for this information
This is where we input the name of the table we want the query to search
For example, the "Students" table within our database
"WHERE" represents the conditions of the search results
This means that we can filter our results by providing a search term
For example, "WHERE `FirstName` = `Bobby`" will only show results that contain Bobby as the value for the FirstName field
"ORDER BY" is the only way to order data in SQL alone
It accepts options such as ASC (ascending) and DESC (descending)
To select all male students and order them by last name we'd do the following:
SELECT `FirstName`, `LastName` FROM `Students` WHERE `Gender` = `Male` ORDER BY `LastName` DESC
"UPDATE" allows us to update data that already exists within a field
For example, UPDATE `Students` SET `Gender` = `Male` WHERE `Gender` = `Female`
This would update our Students table and turn all students male
"DELETE" allows us to delete data from within our database
For example, DELETE * FROM Students
This would delete all records from the Students table
"INSERT INTO" allows us to insert new data in to our database
For example, INSERT INTO Students VALUES (FirstName, LastName, Gender)
This would allow us to insert a new student in to the Students table with appropriate values
A data dictionary is a centralized repository of information for data items used within software (such as databases)
A data dictionary can be seen as a database for another database
The main purpose of a data dictionary is to provide additional information about the data in a database (such as the data's attributes)
Data dictionary's are especially important to developers, as they help maintain an up-to-date reference list items (and their properties) essential to the software
In a relational database management system (RDBMS), a data dictionary also contains information on relationships between the given data and other data within a database
Here are some particular elements that make up a data dictionary. The list below are elements that are known as attributes of a data field in a database. Data dictionary's however have no set limit to how indepth or how long definitions should be; they are fully dependent on the developer(s) of the database.
Name = the name of the data item
Description = a short description given to the data item to help human's understand its purporse
Data Type = read The Computing Teacher's article on Data Types here
Field length = refers to the maximum (and / or minimum) length that the value of the data item can be
Validation rule = ensures the use of correct and clean data by following the given rule
A database is a structured set of data held within a computer
A term is a word or phrase used to describe something or express a concept
A database term is simply a word used to define aspects of a database
In terms of computing, data can be seen as quantities, characters or symbols
A computer is then able to perform operations on these items known as data
In a database, data refers to the values of which are stored within the database structure
A field is the smallest unit of information that can be accessed from within a database
Fields can also be seen as columns, as the title of each field makes up the column's name
Database tables are made up of columns and rows consisting of fields that hold a value
A record is a group of fields in a database that are related to one entry
A record can also be seen as a row
Remember: records (or rows) follow a horizontal path
The word relation is used interchangeably with the word table
They both provide information about an entity in a database
Examples of these would be; customer, order, product,
It was created by E.F Codd the father of relational databases
It goes with tuples (rows), which have attributes (columns) to make up a relation (table). see wikipedia
a system that allows you conduct a full transaction, or no transaction at all.
this needs to happen for booking air tickets, tranferring money on the internet.
it is an atom metaphor, you can't divide the transaction, it all works, or it all does'nt work.
atomicity explained here http://beginnersbook.com/2015/04/acid-properties-in-dbms/
atomicity is to ensure it all works, or it doesn't work.
This a good thing, If you are transferring money to pay a bill or to give to someone. You don't want to be stuck halfway wondering whether it went through or not.
It can do this by monitoring the transaction. Read > Copy > Update.
If a system crashes, it ignores incomplete transactions.
Place these terms in order from largest to smallest; field, entity, attribute
Match the interchangeable words table, row, column, relation, tuple, attribute
Highy recommended for more about databases here
View an extensive list of Database Terms from Raima here
Read more about Database Terminology from About.com here
A database is simply a collection of data or information
A database is constructed in such a way that the data stored is organised and allows for efficient retrieval
Database systems rely on the collection of schemas, tables, queries and reports
There are four types of common databases, including: centralised, distributed, local and online
A centralised database is a database that is located and stored in a single location
It differs from that of other databases due to its singularity
Centralised databases are often stored on one central computer or server
An advantage of this type of database is the ability to access all the information from one central location
A disadvantage of this type of database is the potential bottlenecking if multiple users are trying to access the database at once
A distributed database is a database that is located across multiple locations
Different parts of the database are stored separately on multiple storage devices but controlled by the same DBMS (database management system)
Some types of DBMS's can be used to synchronize data across a distributed database as if the data were hosted in the same location
An advantage of this database type is the ability for users to work with specified parts of the database (hence ensuring security and performance improvements)
A disadvantage of this database type is the complexity of the database, as extra logical and physical design is needed for the implementation of a distributed database system
A local database is simply a database that is stored locally within an application, program or computer storage device
As the name suggests, the database is only accessible locally, and is often used to hold information such as Settings or program-specific information
An advantage of this database type is ability for total security - as the database can only be accessed locally and isn't susceptible to online attacks
A disadvantage of this database type is the fact that information stored on the database can only be accessed locally, and may not be ideal for certain applications or programs (such as web-based applications)
Developers often use local databases when developing and building applications
A local database can also be attached to an external device on a computer, such as a CD or external hard drive
An online database is a database that is accessible only via the Internet or a network connection
Data is often stored in large data centres externally
Many modern websites take advantage of this service to utilise databases for the purpose of dynamic content on websites
An advantage of this type of database is due to the fact that the data can be accessed from anywhere an Internet connection is present
A disadvantage is the availability of the data - as if the host storage centre happens to go offline, the data becomes inaccessible
The primary and foreign key concepts are fundamental to RDMBS
The primary keys within a database are used to define the relationships among the tables (records)
RDBM Systems rely on primary, foreign and composite keys to function
A primary key is a column containing a unique field in each row
This unique value can be used to identify any record in the table
A primary keycannot be NULL, and should utilise an appropriate data type
A common example of a PK is a database with a table called Students
Each row in this table contains a StudentID field
This value is an integer, starting at 20200 and incrementing by one every entry
The 50th entry in the Students table would have a unique identifier (the corresponding StudentID) of 20250
A foreign key is a column (or multiple) containing the values of primary keys from other tables
Foreign keys are barely ever unique and unlike a primary key, may be set to NULL
Foreign keys are used to relate tables, as they establish a link between records
An example related to above would place the StudentID value from our Students table in to a foreign key field called StudentID in our Submissions table
For every submission entered in to the database, a value for StudentID is required
This value depicts which Student has submitted the work
The value corresponds directly to the StudentID in the Student table
The tables are now related to the primary and foreign keys, StudentID
A composite key is a combination of two or more columns in a table
Whilst each field on its own may not be unique, when combined the value of the columns is unique
For example, if a database contained a table called Customers
A column for DOB exists
A column for FirstName exists
A column for LastName exists
A customer named Mary may not be unique, where as 01011970 Mary Smith can be seen as unique
A table's primary key can be made up of composite keys
Read more about Primary and Foreign keys from Microsoft here
Read more about Composite Primary keys from SQLTeam here
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. . (more here)
Excellent article on normalisation here from Microsoft.
Good article here from databasedev uk