Databases and SQl Flashcards
What are the data types to choose from when creating database fields?
⚫️integer
⚫️float
⚫️date / time /Datetime
⚫️Char (FIXED length up to 8,000 characters)
⚫️Varchar (variable length string up to 8,000 characters )
⚫️Text (variable length string up to 2GB)
⚫️autonumber
What are flat file databases?
Stores sa single table of data inside a single text file
What are primary keys ?
A field that stores unique data for each record in a table.
What are some flat file database issues ?
🔵inconsistencies in the data , making it hard to search or sort the data
🔵redundant data, so the databases use more storage/memory than needed
What is a ‘flat file database “?
A database that has all the data stored in one table.
▪️Each record is identified as a new line
▪️flat file databases are often stored using a CSV (comma separated values )format
▪️each field is separated by a comma
What are relational databases ?
Contain multiple tables , each table is known as a relation. Each table has links to others known as relations.
What is a foreign key?
A field in a table that references the primary key of another table.
What is SQL?
Structured query language
The language allows you to create , query, update and delete data to and from databases
Write an SQL query to output all the first names of members and their memberID, who are male
- all normal PSEUDOCODE arithmetic and operational comparatives can be used
How do you select all the fields within a table to output
SELECT *
Wildcard
Write an SQL query to find all the fields of dogs that are older than four (including those that are 4) and whose colour contains the letter ‘B’
Sort this array called members into ascending order of town , up outputting the first name and memberID
Change the age of Molly from 5 to 6
Delete all the black spaniels from the table
Select the owner and dog names for all the four year old dogs
(Multiple tables )
Why are databases used?
They are used to store large amounts of data.
They make retrieving information easy and efficient
What are some advantages of flat file databases ?
All data in one table , so easier to set up and understand
What are some advantages of relational databases ?
🔵data is only stored in one place and then referenced. This means that to input , update or delete pieces of data, it only has to be done in one place which can prevent inconsistencies
🔵only storing the same price of dat once will also save storage space
🔵relational databases can be more secure , some tables can be made more confidential so only some users can see certain data (helps companies meet data protection laws)
What is a data redundancy?
Having the same piece of data stored in multiple places
How do you insert a full record into a table ?
How would insert bits of a record into a table ?
What is a table ?
What is a record ?
The row of a table
What is a field ?
Columns of the table
How do relational databases eliminate data inconsistencies?
Since the data only has to be entered once, and can be referenced through other tables, there is less likely to be any inconsistencies such as spelling mistakes made when entering data in. This will reduce the amount of errors made when making queries
How do relational databases eliminate data redundancy?
Since the data can be referenced from other tables using foreign keys, the same pieces of data do not have to be repeated and stored in multiple places, there are no longer pieces of data that are being repeated unnecessarily
