Unit 11 - Databases and Software Development Flashcards
Define entity
A category of object, person, event or thing of interest about which data needs to be recorded
Define attribute
A component of an entity
Define primary key
An identifier for a specific record
Define composite primary key
An identifier for a specific record that requires two or more attributes to uniquely identify it
What are the three types of relationships that entities can have with each other?
- One-to-one
- One-to-many
- Many-to-many
What two relationships are not used in databases?
One-to-one and many-to-many
Define foreign key
An attribute that is made to join two tables
Why aren’t many-to-many and one-to-one relationships used within databases?
One-to-one relationships are not used because they are redundant
Many-to-many relationships are not used because they will screw up the database causing problems with data redundancy, data insertion, deletion and updating which means that using them would make the system impossible to operate or navigate
Define relationship
A link between two entities
Define relation
A visual representation of entities within a database
Define field
An item of data
Define record
A row of data within a relationship
How is a relation organised?
A relation contains information about a single topic and is the entity with rows holding records relating to specific sections of information and each field in a row representing an attribute
Define a flat file
When all of the data is held in one table so there are no relationships and only one entity (therefore it is often just information about one entity)
Define normalisation
A process which enables programmers to come up with the best possible design for a database by reducing data duplication and improving data integrity
Describe first normal form
All of the attributes are atomic and there are no repeating attributes or groups of attribute
Describe second normal form
There are no partial dependencies within the relation which is acheived by using a composite primary key and ensuring that all of the attributes are dependent on all parts of the composite key (this means if you change the key or part of the key you change every attribute in that entity)
Describe third normal form
There are no non-key dependencies which means that there are no non-key dependencies (meaning that one non-key attribute changes and nothing else should change)
What is a non-key dependency?
An attribute which is dependent on another attribute that is not part of the key
Why is normalisation beneficial to the database?
- It is easier to maintain a normalised database
- It is easier to edit a normalised database
- There is no duplication of data so no memory is wasted
- Data integrity is maintained
- Search time is decreased
What does SQL stand for?
Subject query language
Is SQL a declarative or an imperative high level language?
Declarative
What is SQL used for?
Creating and maintaining tables in Python
How do you use a SELECT statement?
SELECT (list of fields)
FROM (the table of list of tables)
WHERE (list the search criteria)
ORDER BY (list how to sort and display data)
How do you refer to all fields or all character?
‘*’
What does BETWEEN mean in SQL?
In an inclusive range
What does IN mean in SQL?
Specify multiple possible value for a column
What character is placed at the end of each statement in SQL if more than one statement is one a line?
’;’
What criteria needs to be met in order to extract data from multiple tables in the same command?
The tables must be linked by foreign keys (they must have a relationship with one another)
What do you need to specify when extracting data from more than one table?
Which table the data from the foreign key is being extracted from if it is selected
Define VARCHAR(n)
A string with variable length, max length of n
Define CHAR(n)
A string of fixed length n
What problem arises in client-server databases?
Multiple people trying to access the same record at the same time
How does record locking work?
When one person is editing a record it is sealed to everybody else so they cannot edit it which prevents data from being lost or inconsistencies arising in the data
How does serialisation work?
Each object in the database is given a read timestamp and a write timestamp, when someone is editing an object and they save their changes, if the timestamp is not the same as when they started the changes will be cancelled as it means someone else has changed the object within that time
How does commitment ordering work?
Transactions in a system are ordered in terms of their dependencies on one another and the time they were initiated, this means that if someone is editing an attribute, it and all of the other attributes dependent on it will be locked to one another until the transaction has ended
What are the five stages of the system life cycle?
Analysis, design, implementation, evaluation and maintenance
What will an analyst do in order to understand the problem?
Define the problem by asking questions about the data, procedures, future and problems
How is the development of a system guided?
By feedback from the client
What are the disadvantages of an agile approach to software development?
This method is time consuming and becomes increasingly expensive
What does the analysis stage entail?
Ensuring that the team developing the system understand fully what the client wants
For what reasons might a system be updated?
- The system cannot cope with the increased volume of information it needs to handle
- New technology means the system has become outdated and new technology provides new opportunities
- The current system is inflexible or inefficient
- Demand has decreased (the system no longer appeals commercially)
- New platforms and operating system
- Increased processing power
- Increased network power
How can software developers gather new information?
Interviews, surveys, observing the current system and examining the current system
What is a feasibility study?
An analysis of whether it is possible or desirable to create a new system
What do you do to indicate a date in SQL?
Put hashes at either end of the date
How does timestamp ordering work?
Each transaction has a read and write timestamp that indicates the last time the data was read from or written to
