04 databases Flashcards

Question

what is a 1:M relationship

Answer 1

- one owner can have multiple pets

Answer 2

- one actor can appear in many films and one film can have many actors. - they need to be resolved to remove the M:N relationship

Answer 3

An anomaly is when you try to modify a table, some side-effects may follow. They can arise in tables that have not been modified.

Answer 4

1. When data is added and not all the data is known, some fields will be null. 2. When data is repeated in the table.

Answer 5

When a record is deleted and results in the loss of other data that only occurs in that record.

Answer 6

flat-file databases can cause lots of problems with the data. They can cause update, insertion and deletion anomalies to occur as it is one big related table, instead of small little related tables.

Answer 7

A problem that occurs when data that is repeated in a number of records that requires updating. If all records are not updated the data could become inconsistent or inaccurate.

Answer 8

It holds its data over a number of tables instead of one. Records within the tables are linked (related) to records held in other tables.

Answer 9

Means that foreign keys in a table should have a valid corresponding primary key in another table. Therefore, in the case of the First name DVD rentals, for every custom, there should be at least one or more records in the DVD rental file

Answer 10

1. Understanding the database structure 2. Database design guidelines 3. Querying and manipulating data 4. Data relationships and constraints 5. Database security and access control 6. Troubleshooting and performance optimisation 7. Collaboration and knowledge sharing

Answer 11

It is to provide comprehensive and detailed information about the structure, design and functionality of the database. It serves as a reference and guide for developers working on the database.

Answer 12

Documentation helps developers understand the overall structure of the database, including tables, fields, indexes, and relationships. It provides a clear picture of how data is organized and stored within the database.

Answer 13

Documentation outlines the design principles and guidelines followed while creating the database. It explains the rationale behind the design choices, such as table structures, data types, naming conventions, and normalization techniques. This helps developers adhere to consistent design standards and make informed decisions when modifying or extending the database schema.

Answer 14

Documentation includes information about the available queries, stored procedures, functions, and other database operations. It provides details on how to retrieve, insert, update, and delete data from the database, along with examples and best practices. This helps developers write efficient and optimized database queries.

Answer 15

Documentation describes the relationships between different tables using primary keys, foreign keys, and other constraints. It explains how these relationships should be maintained and how referential integrity is enforced. Developers can refer to this information to understand the dependencies between tables and ensure data consistency.

Answer 16

Documentation may include information about the security measures implemented in the database, such as user roles, permissions, and authentication mechanisms. It guides developers on how to handle sensitive data and implement proper access controls to protect the integrity and confidentiality of the database.

Answer 17

Documentation may contain troubleshooting tips, common issues, and performance optimization techniques specific to the database. It helps developers diagnose and resolve database-related problems efficiently, improving the overall performance and reliability of the system.

Answer 18

Database documentation serves as a centralized source of information that can be shared among developers, database administrators, and other stakeholders. It promotes collaboration, consistency, and knowledge sharing within the development team, ensuring everyone has access to accurate and up-to-date information about the database.

Answer 19

Conceptual data modelling: examines the business's operations, intending to create a model with the most important parts (such as describing a store's order system). Essentially, this data model defines what data the system will contain. Example: Data Flow Diagram (no longer taught in ATAR Computer Science). Logical data modelling: is more detailed than a conceptual model. A logical model includes information about the data entities and their relationships. However, a logical design model is independent of any technology on which its design could be implemented Example: Entity-Relationship Diagrams. Physical data modelling: examines how the database will actually be implemented, intending to model how the database elements will interact with each other. Here, the actual database is created; the schema structure is developed, refined, and tested. Data models generated should support key business operations. Example: Data Dictionary

Answer 20

- entity name - element name - data type - size/format default - description - constraint (PK, FK, not null)

Answer 21

create a relational database and link them together with primary and foreign keys.

Answer 22

An ERD is a visual representation that illustrates the relationships between entities in a database. An ERD uses various symbols and connectors to represent entities, attributes, and relationships between them.

Answer 23

Entities It is typically depicted as a rectangle in an ERD. Each entity has attributes that describe its characteristics. Attributes They are properties or characteristics of an entity. They provide detailed information about the entities and are represented within the column on the right below the name. Attributes can be simple or composite and can have different data types. Keys Represented in the left column below the name of the ERD. They either have PK (primary key) or FK (foreign key) Relationships Relationships represent the associations between entities. They indicate how entities are connected or related to each other. They are represented by crows feet, that connect one entity to another. Cardinality It defines the number of instances of one entity that can be associated with the instances of another entity. It specifies the relationship's multiplicity or how many entities participate in the relationship. 1:1, 1:M, M:N

Answer 24

You should use the crows foot notation 1:1 ------------------------ 1:M -----------------------< M:N >---------------------<

Answer 25

It is a measure of the condition of data based on factors such as accuracy, completeness, consistency, reliability, and whether it's up to date.

Answer 26

- Data accuracy - Data completeness - Data authenticity - Data relevance - Data consistency - Data currency - Data cleaning

Answer 27

1. Financial loss - incorrect transactions, missed opportunities and poor investments 2. Damage to reputation - can damage organisation's reputation 3. Poor-decision making - long-term consequences

Answer 28

1. Identify inaccurate data 2. Document inaccurate data 3. Fix inaccurate data

Answer 29

1. Improved decision making 2. Increased efficiency 3. Enhanced reputation

Answer 30

1. Implement data quality checks 2. Use data governance 3. Monitor data quality 4. Train staff

Answer 31

is how much required data is included in a data set

Answer 32

the quality of data being genuine and unaltered since creation

Answer 33

how useful and applicable data is for a specific task

Answer 34

The absence of contradictions or conflicts between data values across different systems or data sets.

Answer 35

The degree to which data is up-to-date and reflects the most recent information available.

Answer 36

Data accuracy - how well the data corresponds to the real-world. - focuses on correctness and truthfulness of data Data validity - degree to which data values are consistent with a defined domain. - focuses on whether the data falls within a given domain and adheres to defined rules

Answer 37

Reviewing data from a source and summarising its details.

Answer 38

Comparing data from different sources to find duplicates or similarities.

Answer 39

Correcting inaccurate records in a database.

Answer 40

Software that helps maintain high data quality

Answer 41

Rules and responsibilities for managing data and information processes.

Answer 42

process of identifying and eliminating data anomalies and redundancies. it is a set of 4 steps aimed at removing repeating data to improve data efficiency.

Answer 43

1. columns must have single values 2. columns must have unique names 3. values of a given attribute must be the same data type 4. no two records or rows are identical

Answer 44

1. identify multi-valued fields 2. create a second entity 3. identify primary key for the second entity 4. add a foreign key to the other table

Answer 45

1. the table is in 1NF 2. there is no partial dependencies of any column on the primary key

Answer 46

1. the table is in 2NF 2. all non-primary fields are dependent on the primary key

Answer 47

the table name and its attributes a key is an attribute that is unique

Answer 48

CREATE TABLE name ( column1 DATATYPE, column2 DATATYPE, column 3 DATATYPE, );

Answer 49

CREATE TABLE parent_table ( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE child_table ( id INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES parent_table(id) );

Answer 50

- integer - real - text - blob - numeric - boolean - date - datetime

Answer 51

CREATE TABLE Purchase ( Product TEXT, PDate DATE, Price REAL, Quantity INTEGER ); INSERT INTO Purchase (Product, PDate, Price, Quantity) VALUES ('Bagel', '2021-10-21', 1, 20), ('Banana', '2023-03-10', 0.5, 10), ('Banana', '2023-10-10', 1, 10), ('Bagel', '2023-10-25', 1.5, 20);

Answer 52

SELECT FROM WHERE

Answer 53

SELECT DISTINCT FROM WHERE

Answer 54

SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200

Answer 55

SELECT avg(price) FROM Product WHERE maker=“Toyota” SELECT count(*) FROM Product WHERE year > 1995

Answer 56

INSERT INTO R(A1,…., An) VALUES (v1,…., vn) INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)

Answer 57

UPDATE customers SET contact_name = 'Fred Schmidt', phone_number = '042459823’ WHERE contact_name = 'Alfred Schmidt

Answer 58

DELETE FROM PURCHASE WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’

Answer 59

1. collection 2. access 3. usage 4. storage 5. transfer 6. deletion & destruction

Answer 60

Collecting and analysing data can be useful for an organisation to promote productivity, growth, competition and innovation. The Privacy Act 1988, encompasses the protection of people’s personal information. There are 13 Privacy Principles under the Privacy Act. Personal information is information that identifies you or could identify you.

Answer 61

Only provide data that is required when signing up to services Encrypt your data when sending across a network Backup your data Make your old computers' hard drives unreadable through destruction and/or data shredding Secure your wireless networks with latest Wi-Fi protection Use a robust firewall along with Anti-virus or anti-malware protection Encrypt data on your USB drives and SIM cards Disable file and media sharing if you don't need it Create encrypted volumes for local and portable drives Lock down your computers UEFI with a password Overwrite/shred deleted files Delete old files from cloud backups check and configure app privacy and permissions settings on your phone Configure privacy setting for online services Enable remote location and device-wiping Lock your smartphone, tablet, laptop and desktop devices

Answer 62

Ensuring that critical data is backed up regularly is essential for keeping an organization up and running, no matter what happens. A backup plan is usually part of a disaster recovery plan (DRP) There are two broadly defined approaches to backup on-premises backup remote backup. In an on-premises setup, you can copy your data to a second hard drive, other media, or a shared drive, either manually or at specified intervals. This is then taken off-site at the end of the day In remote backup, your computer automatically sends your data to a remote center either as soon as modifications have been made to a file or at specified intervals.

Answer 63

Full Backup - the most complete type of backup but is more time-consuming and requires more storage space than other backup options. Incremental backup - only backs up files that have been changed or newly created since the last incremental backup. This is faster than a full backup and requires less storage space. However, in order to completely restore all your files, you'll need to have the most recent full backup and all incremental backups since then available. And in order to recover a specific file, you may need to search through several incremental backups. Differential backup - backs up the files that have been changed or newly created since the last full backup. To recover the back, both the full backup and the differential backup are needed

04 databases Flashcards

(88 cards)