The Basics of Databases

Simple Database

 The simplest kind of database is a flat file, consisting of information about a single entity.




Entities

 An entity is a category of object, person, event or thing of interest about which data needs to

be recorded. Eamples of entities would be: Customer, Product or Subscription (for a digital textbook service).




Records

 In a database, a record is a group of fields within a table that are belong to a specific entity.

The different fields in a record for the digital textbook example could be:

  • Customer (custID, title, firstname, surname, email)
  • Product (productID, title, subject, level, price)
  • Subscription (subID, startDate, endDate)

Primary Key


Each entity needs an identifier which uniquely identifies a particular record. This is called a Primary Key. 

Each Primary key is totally unique for every record.



Composite Primary Key

Sometimes multiple unique IDs are required to identify a record (if there was a order number and product number for example). These are called Composite primary keys. 

When writing Primary Keys in an exam situation you identify them by underlining them.



Secondary Key

Secondary keys are a second way of searching a database. They are not unique to a record but will narrow down results. For example a book author's name good be searched in a library database. 





Entity Relationships

Entities are usually linked in some way. This is represented using diagrams. 



We can say, for example, that one school has many pupils, or many pupils attend one school.



A customer can have multiple subscriptions (many subscriptions), of which each subscription is linked to one product. 



Foreign Key

A foreign key is an attribute that creates a join between two tables (relations). 



Referential Integrity

This means that no foreign key in one table can reference a non-existent record in another. 

For example: it should not be possible to add a subscription for a customer with custID C100 if there
is no record for customer C100. 



Relational Databases


Data is held in separate tables are linked by common attributes.

A row on a table is called a record.

The columns in the tables represent different attributes.



Indexing

Database software automatically keeps a record of all Primary keys. This allows for records to easily be searched for.

Sometimes other terms can be used to search the table. Such as secondary keys (book author for example). This will speed up and make searching easier.


Normalisation

Normalisation is used to come up with the best possible design for a database.

This means data is not duplicated, data is consistent throughout the database and the structure should be flexible to allow the user to carry out complex queries. 

Comments

Post a Comment

Popular posts from this blog

CPU Fetch-Decode-Execute Cycle

Image
A CPU (central processing unit) executes and manages the rest of the hardware inside a computer system - it is the brain of a computer! The CPU is made up of the main memory, the processor and the cache memory. It's job is to process data (by processing we mean searching, sorting, calculating and decision making). The CPU follows the Fetch-Decode-Execute cycle. The Fetch Stage: the CPU use a piece of hardware called the ' address bus ', this is where the CPU asks the RAM for some data. The RAM then responds with this data on the ' Data Bus '.  The Decode Stage: This is where the CPU converts the instructions in to a way in which it understands it. It then gets things ready for the next step.  The Execute Stage: This is where data processing happens. Instructions are carried out on the data, as soon as the command is completed it starts the next one. 
Read more

Scheduling

Image
Scheduling is where a computer prioritises tasks to be carried out in a certain order to maximise the use of the computer and all of its peripherals. The algorithm must be robust so the computer does not freeze and also so every task/user gets a fair response time. Two main types of jobs are I/O bound jobs and processor bound jobs: I/O bound jobs (input/output) examples: Sending a document to the printer. Moving the mouse. Typing on the keyboard. Processor bound jobs examples: Playing Music Watching a video Calculating in Excel. There are different ways a processor can prioritise tasks: FCFS (first come first served) - tasks are started and completed in the order they are received. RR (round robin) – the time slice is 1 minute - each job has a minute spent on it before moving onto the next one. This occurs in a circuit until all jobs are complete. SJF – shortest job first - The job with the shortest completion time is carri
Read more

Utility Software

Image
Computer security Anti-virus - Scans/analysing your system to check for virus’ on a regular basis, if it identifies a virus that it knows of (in its database) it will remove it from the system. Spyware protection - Scans user files, checks for damage caused by installed spyware to the system and analyses domain names that potentially contain spyware. They then remove or warn the user about issues.   Firewall - use to prevent specific network packets making it onto a network. These may be blocking people from accessing certain websites or from certain files from being downloaded/uploaded. Disk organisation Defragmenter - rearranges files stored on the hard drive so related files are stored together. This makes it easier for the CPU to find files easier to find and results in much faster performance. File transfer - software designed to transfer content from one device to another. Either done from one piece of hardware to another using physical cables or done using F
Read more