Unit 7 Relational databases and SQL Flashcards
When are databases used?
- Databases are used in many business situations
- They are also used at the back-end of many cloud services
Explain some places where databases are used
School use:
- learning platforms such as Google Classroom
- management information systems such as SIMS or iSAMS
Organisation and business use:
- customer relationship management (CRM) software
- online calendars such as Google calendar
- the backend of most website to dynamically generated pages
Personal use:
- social media data
- multiplayer games
What is a database?
- A database is a structured way to store data so that it can be retrieved using queries
- Contains one or more tables
Data types
Just as when programming variables, database fields need to have a data type. The possible database data types to choose from are:
- Integer (whole number)
- Real, float, decimal (number with a decimal component)
- Data, Time, Datetime (to store dates and times)
- Char (fixed length string up to 8,000 characters)
- Varchar (variable length string up to 8,000 characters)
- Text (variable length string up to 2 GB of data)
Flat file databases
- A flat file database stores a single table of data inside a single text file
- Flat file databases are often stored using a CSV (comma separated values) format
- Each record appears on a separate line, each field is separated by a comma
- This format is very easy to set up, however, it is hard to manage for anything but the simplest of data sets
What is a primary key?
A field that stores unique data for each record in a table
Relational databases
- A relational database contains multiple tables that are linked together using relationships
- Relational databases allow us to design tables that reduce inconsistencies and eliminate data redundancy
What is data redundancy?
When there is unnecessary data repetition in a database
What is a foreign key?
A field in a table that references the primary key of another table
What are the three possible relationship between tables in a database?
Relationships between tables can be:
- one-to-one
- many-to-many
- one-to-many
What does SQL stand for?
Structured Query Language
What is SQL?
- A language which allows you to create, query, update and delete data to and from databases
- SQL is used with most Database Management Systems, including MySQL, SQL Server and MS Access
- SQL queries can be used within high level programming languages such as Python
Writing a query using SQL
The SQL syntax for querying a database is:
SELECT… list the fields to be displayed
FROM… specify the table name
WHERE… list the search criteria
What are the benefits of using relational databases?
They eliminate data redundancy and inconsistency
What are the benefits of using relational databases?
They eliminate data redundancy and inconsistency
What is SQL?
A language used for querying and updating database tables in a relational database
When is the SELECT statement used?
To extract a collection of fields from one or more tables in a database
What is the syntax of the SELECT statement?
SELECT list of fields to be displayed
FROM list the table or tables where the data will come from
WHERE list of search criteria
ORDER BY
list the fields that the results are to be sorted on (either ASC or DESC)
What is the default setting for ORDER BY?
Ascending
What does the INSERT INTO statement do?
Used to insert a new record into a database table
What is the syntax for the INSERT INTO statement?
INSERT INTO tableName (column1, column2,…)
VALUES (value1, value2,…)
What does the DELETE statement do?
Used to delete a record from a database table
What is the syntax for the DELETE statement?
DELETE FROM tableName
WHERE column = value
What does the UPDATE statement do?
Used to amend a record in a database table
What is the syntax for the UPDATE statement?
UPDATE tableName
SET column1 = value1, column2 = value2, …
WHERE column = value
What problems can data redundancy cause?
- An unnecessary amount of data storage capacity required to store the duplicated data
- An increased risk of having inaccurate data
- It is more time consuming and problematic to update the database
