Databases Flashcards
What is a data base
collection of data items structured so that data can be searched and retrieved when necessary.
What is a flat file structure
where the entire database is stored in a single file or table and there is one row for each record and one column for each field. This type of structure leads to data being repeated unnecessarily
What is data duplication
Duplicate copies of data – or duplicate copies of non-key data.
What is data inconsistency
The more often data must be keyed the more chance there is for errors in data entry
What is poor data integrity
Data that is erroneous or inconsistent is not reliable.
What is a relational database structure
allow data to be stored across a number of tables. The tables are linked or related together, using relationships, on common fields.
Does a relational data base need multiple tables
Yes
What is access rights
Most relational database systems provide access rights which can be assigned to different users depending on their role. Some of the operations that can be allowed or disallowed to a user are SELECT, INSERT, DELETE, ALTER, and CREATE.
What is sql
Structured Query Language
When manipulating data in the database, SQL queries are formulated.
What is a logical data model
The logical data model is produced during the design of the database, and is independent of the database software,
What is a physical data model
A physical data model is produced during the implementation of the database and defines the physical structure of the database. It creates the table structures, along with the columns (fields) inside them, and the data type for each field. The primary key and any foreign or composite keys for each table are specified here as well as any appropriate validation rules.
What is an attribute
Attribute
An individual data item within an entity. For example, Employee Name in an entity called EMPLOYEE. This is also known as a field.
What is an entity
Identifies an object or subject about which data will be stored within the system. A person, place, thing, or event about which data is collected and held in a table. For example, an EMPLOYEE or a PROJECT. An entity is represented as a table.
What is a primary key
A primary key is an attributes that uniquely identifies one record in a table. For example, the Employee No would uniquely identify each employee in a table.
What is a composite key
Consists of two or more attributes that uniquely identifies one record in a table
What is a relationship
A relationship is an association of entities. A relationship is established by a foreign key in one entity linking to the primary key in another.
What different relationships can you have
One-to-One (1:1)
• One-to-Many (1:M)
• Many-to-Many (M:N)
What is referential integrity
ensures that the data in one table does not contradict the data in another table. Specifically, every foreign key value which exists in a table must have a matching primary key value in a related table.
What is an ER diagram
method to graphically model and design relational databases. ER diagrams will represent real world objects and the links, or relationships, between them.
What is normalisation
Normalisation is a technique of organising the data in the database to eliminate problems such as data redundancy and data inconsistency and improving data integrity.
What the rules of UNF
Select a name for the main entity – in this case PROJECT.
2. Each field contained within the entity is listed in brackets.
3. Primary keys are underlined.
4. Foreign keys are represented with *.
5. Repeating groups are contained within {}.
6. Derived attributes such as average Hourly Rate should not be included here but the developer may decide to store the value to improve performance. By derived, it means that the hourly rate is decided (or derived) based on the Pay Grade.
What are the rules for 1 nf
all attributes must be atomic (Atomic means that the attribute cannot be further sub divided).
• entities must not have repeating groups.
What are the rules of 2nf
• it fulfils the conditions for 1NF – all repeating groups are removed
• all non-key attributes (e.g. attributes which aren’t primary/foreign keys) in the relation are fully dependent on the primary key.
What are the rules for 3 nf
it is in 2NF – there are no partial key dependencies
• there are no non-key dependencies.
Advantage to normalised data
The resulting database will take up less storage space because the duplication of data is minimised. Data is only stored once. The advantages of this are
• No multiple record changes needed
• More efficient storage
• Simple to delete or modify details.
• All records in other tables having a link to that entry will show the change.
2. Information retrieval will be more efficient because data is structured effectively. Queries will be processed faster as complex queries can be carried out using SQL.
3. Less redundancy means less inconsistencies in data because data will only have to be entered once.
4. Data integrity will be increased.
Disadvantages to normalised data
• Normalisation is a complex process required to create the database structure.
• Normalisation can generate more tables than an un-normalised database.
• More tables mean a more complex database and queries may be slow to run.
• It is necessary to assign more relationships to interact with larger numbers of tables.
• With more tables, setting up queries can become more complex.
