CS UNIT 8

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson
Download our mobile app to listen on the go
Get App

Questions and Answers

Explain how data redundancy is addressed in a relational database system compared to a file-based approach, and why this is advantageous.

In relational databases, data redundancy is minimized by storing data in separate, linked tables, avoiding duplication and saving storage space. This also ensures consistency across applications.

A school database has a Student table and a Class table. Describe how a relationship can be formed between these two tables and why this design is important for referential integrity.

A relationship can be formed by including a foreign key from the Class table in the Student table. This ensures that each class ID in the Student table corresponds to an existing class in the Class table.

Define the concept of a composite key in a relational database, and provide an example.

A composite key is a set of two or more attributes that uniquely identifies a record in a table. An example is the StudentID and SubjectName in a StudentSubject table.

Differentiate between a candidate key, a primary key, and a foreign key in a relational database. Provide context for each.

<p>A candidate key is an attribute or set of attributes that can uniquely identify a record; a primary key is the chosen candidate key for unique record identification; a foreign key refers to the primary key in another table to establish relationships.</p>
Signup and view all the answers

Explain the significance of indexing in a database and how it can impact the performance of querying and retrieving data.

<p>Indexing speeds up data retrieval by creating a data structure from one or more columns, allowing the database to quickly locate records without scanning the entire table.</p>
Signup and view all the answers

What is an E-R diagram, and how does it aid in the process of database design?

<p>An E-R diagram is a graphical representation of a database, showing entities and their relationships, that aids in visualizing the database structure and relationships.</p>
Signup and view all the answers

Describe the objectives of database normalization and how it helps address potential issues in database design.

<p>Normalization aims to reduce data redundancy and improve data integrity by organizing data into tables with defined relationships, preventing update anomalies and data inconsistencies.</p>
Signup and view all the answers

Briefly describe what each normal form (1NF, 2NF, 3NF) represents in the context of database normalization.

<p>1NF: eliminates repeating groups; 2NF: removes partial dependencies; 3NF: eliminates transitive dependencies.</p>
Signup and view all the answers

Explain why a database table might not be in 1NF and give an example of how to normalize it.

<p>A table may not be in 1NF if it contains repeating groups of attributes. For example, a table with multiple <code>SubjectName</code> and <code>SubjectTeacher</code> columns can be normalized by creating a separate <code>Subject</code> table and linking it with a foreign key.</p>
Signup and view all the answers

Outline a scenario where a table is in 1NF but not in 2NF, and describe the steps required to achieve 2NF.

<p>A table is in 1NF but not 2NF if non-key attributes depend on only part of the primary key in a composite key. To achieve 2NF, create new tables to isolate the partial dependencies.</p>
Signup and view all the answers

What are transitive dependencies in a database, and why must they be removed to achieve 3NF? Give an example.

<p>Transitive dependencies occur when non-key attributes depend on another non-key attribute. They must be removed to ensure each attribute depends only on the primary key, avoiding inconsistencies.</p>
Signup and view all the answers

Describe a scenario where a school database includes student details and subject choices, and briefly outline the steps to normalize this database to 3NF.

<p>Separate student information, subject details, and subject-student relationships into distinct tables. After that, eliminate partial and transitive dependencies.</p>
Signup and view all the answers

What are the benefits of adopting a database management system (DBMS) over using a file-based approach?

<p>DBMS offers reduced data redundancy, improved data consistency, controlled access, and better querying capabilities compared to file-based systems.</p>
Signup and view all the answers

A company stores sensitive customer data in a database. Describe three security measures a DBMS can implement to protect this data.

<p>User authentication, access rights control, data encryption.</p>
Signup and view all the answers

Explain the purpose and benefits of using a data dictionary in a DBMS.

<p>A data dictionary stores metadata, improving data integrity by defining tables, attributes, relationships, and validation rules.</p>
Signup and view all the answers

How does a DBMS's query processor facilitate the retrieval of information from a database, and what are its primary components?

<p>By interpreting SQL queries through the DDL interpreter, compiling DML statements into optimized instructions via a DML compiler, and executing them using a query evaluation engine.</p>
Signup and view all the answers

Describe the roles of a DDL interpreter, a DML compiler, and a query evaluation engine within a DBMS query processor.

<p>DDL: Processes and records data structure definitions; DML: optimizes data manipulation instructions; Query evaluation engine: executes the instructions to retrieve/modify data.</p>
Signup and view all the answers

What is the primary function of a data definition language (DDL), and provide some examples of SQL DDL commands?

<p>Creating, modifying, and removing data structures. <code>CREATE TABLE</code>, <code>ALTER TABLE</code>, and <code>DROP TABLE</code>.</p>
Signup and view all the answers

Explain the purpose of a data manipulation language (DML) and give examples of SQL DML commands.

<p>Add, modify, delete, and retrieve data. <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code>.</p>
Signup and view all the answers

Explain the difference between DDL and DML, and why both are necessary for database management.

<p>DDL handles structure, DML handles data. Both are needed to define and interact with a database effectively.</p>
Signup and view all the answers

A database has a Student table with attributes such as StudentID, FirstName, LastName, and ClassID. Write an SQL statement to create this table.

<p><code>CREATE TABLE Student (StudentID CHARACTER, FirstName CHARACTER, LastName CHARACTER, ClassID CHARACTER);</code></p>
Signup and view all the answers

How would you add a primary key constraint to the StudentID attribute in the Student table using SQL?

<p><code>ALTER TABLE Student ADD PRIMARY KEY (StudentID);</code></p>
Signup and view all the answers

Write an SQL query to select the FirstName and LastName of all students from the Student table who are in ClassID 7A, ordered by LastName.

<p><code>SELECT FirstName, LastName FROM Student WHERE ClassID = '7A' ORDER BY LastName;</code></p>
Signup and view all the answers

Using SQL, describe how to insert a new student record into the Student table with StudentID S1301, FirstName Peter, LastName Probert, and ClassID 7A.

<p><code>INSERT INTO Student VALUES ('S1301', 'Peter', 'Probert', '7A');</code></p>
Signup and view all the answers

Explain how the WHERE clause is used in SQL queries and provide an example of how to use it to filter data.

<p>Specify conditions. Show only Students = '7A'.</p>
Signup and view all the answers

How is the ORDER BY clause used in SQL, and what is its impact on the output of a SELECT statement?

<p><code>ORDER BY</code> sorts results. <code>SELECT</code> ordered by lastName will show lastName from A-Z.</p>
Signup and view all the answers

What aggregate functions can be used in SQL, and what is their role in data analysis?

<p><code>SUM</code>, <code>COUNT</code>, <code>AVG</code>. They are helpful to total and analyze data.</p>
Signup and view all the answers

Describe a file-based approach for managing data and outline its key limitations.

<p>A file-based approach stores data in individual files. Limitations include data redundancy, inconsistency, and dependency on specific applications.</p>
Signup and view all the answers

Explain the purpose of a database management system (DBMS) and outline its primary functions.

<p>DBMS manages and organizing data. Creating, modifying, and querying data are the main objective.</p>
Signup and view all the answers

Describe the advantages of using a relational database model compared to a file-based approach.

<p>A relational database reduces redundancy and improves data consistency. Different applications can share data.</p>
Signup and view all the answers

Outline the key terminology associated with a relational database model (e.g., table, record, field, entity, attribute).

<p>A table is an organized collection of data, a record is a row representing an instance, a field is a column for data, an entity is a distinct object, and an attribute is a characteristic of an entity.</p>
Signup and view all the answers

Explain how to document database design using entity-relationship (E-R) diagrams.

<p>E-R diagrams visually represent entities and relationships in a database, helping define structure and connections.</p>
Signup and view all the answers

What is normalization in the context of database design, and why is it important?

<p>Normalization organizes data to minimize redundancy. Improves data integrety and efficiency.</p>
Signup and view all the answers

Describe the key features provided by a database management system (DBMS).

<p>Data integrity, access control, backup/recovery, and query processing.</p>
Signup and view all the answers

Outline the types of software tools typically provided by a DBMS.

<p>Query tools, report generation tools, data import/export tools, and security management tools.</p>
Signup and view all the answers

Explain how to create and modify a database structure using a Data Definition Language (DDL).

<p>Use DDL commands such as CREATE, ALTER, and DROP to define tables, relationships, and constraints.</p>
Signup and view all the answers

How are queries and maintenance of a database performed using a Data Manipulation Language (DML)?

<p>DML commands like SELECT, INSERT, UPDATE, and DELETE are used to retrieve, add, modify, and remove data.</p>
Signup and view all the answers

Describe the use of SQL as both a DDL and a DML.

<p>SQL is used for defining database structures and manipulating stored data.</p>
Signup and view all the answers

Explain how to understand a given SQL script.

<p>Analyze the script's structure and commands to interpret its intent and data interactions.</p>
Signup and view all the answers

How do you create back ups using a DBMS?

<p>By copying all database data to a separate medium, so that is is available, should there be data loss.</p>
Signup and view all the answers

Flashcards

Database

A structured collection of data items accessible by different applications.

Relational database

A database where data items are linked using internal pointers, minimizing redundancy.

Table

A group of similar data in a database, organized into rows and columns.

Record

A row in a table in a database, representing a single instance of an entity.

Signup and view all the flashcards

Field

A column in a table in a database, holding a specific attribute of the entity.

Signup and view all the flashcards

Tuple

One instance of an entity, represented by a row in a table.

Signup and view all the flashcards

Entity

Anything about which data can be stored, such as a person, place, or event.

Signup and view all the flashcards

Attribute

Individual data item stored for an entity, e.g., name, address, or date of birth.

Signup and view all the flashcards

Candidate key

An attribute or smallest set of attributes where no tuple has the same value.

Signup and view all the flashcards

Primary key

Unique identifier for a table; a special case of a candidate key.

Signup and view all the flashcards

Secondary key

A candidate key that is an alternative choice to the primary key.

Signup and view all the flashcards

Foreign key

A set of attributes in one table that refers to the primary key in another table.

Signup and view all the flashcards

Relationship

Situation where one table has a foreign key referencing a primary key in another table.

Signup and view all the flashcards

Referential integrity

Property of a database ensuring foreign key values match corresponding primary keys.

Signup and view all the flashcards

Index (database)

Data structure built to speed up data searching within a database table.

Signup and view all the flashcards

Entity-relationship (E-R) model

A graphical representation of the database structure and relationships between entities.

Signup and view all the flashcards

Normalization (database)

Process of organizing data into tables to minimize redundancy.

Signup and view all the flashcards

First normal form (1NF)

Status of a relational database where entities do not contain repeated groups of attributes.

Signup and view all the flashcards

Second normal form (2NF)

Status of a relational database in 1NF where non-key attributes depend on the primary key.

Signup and view all the flashcards

Third normal form (3NF)

Status of a relational database in 2NF where all non-key attributes are independent.

Signup and view all the flashcards

Composite Key

A set of attributes forming a primary key to provide a unique identifier for a table.

Signup and view all the flashcards

Database Management System (DBMS)

Systems software for the definition, creation, and manipulation of a database.

Signup and view all the flashcards

Data management

The organization and maintenance of data in a database to provide the information required.

Signup and view all the flashcards

Data dictionary

A set of data that contains metadata for a database.

Signup and view all the flashcards

Data modeling

Analysis and definition of data structures required and needed in a database to produce a diagram.

Signup and view all the flashcards

Access rights (database)

permissions given to database users to access, modify, or delete data

Signup and view all the flashcards

DBMS developer interface

Commands required for definition, creation and manipulation of a database.

Signup and view all the flashcards

Structured Query Language (SQL)

Standard query language used with databases to define attributes and modify.

Signup and view all the flashcards

Query processor

A DBMS feature that processes and executes queries written in program language.

Signup and view all the flashcards

Data definition language (DDL)

A language used to create, modify, and remove the data structures that form a database.

Signup and view all the flashcards

Data manipulation language (DML)

A language used to add, modify, delete, and retrieve data stored in a relation database.

Signup and view all the flashcards

SQL script

A list of commands that perform a given database task.

Signup and view all the flashcards

Study Notes

  • Databases are used to store large amounts of data in an organized way.

Limitations of File-Based Approach

  • Storage space is wasted due to data duplication across separate applications.
  • Data inconsistency arises when updates in one application aren't reflected in others.
  • Enquiries depend on data structure and software, lacking independence.

Advantages of Relational Databases

  • Solves data redundancy issue by storing data in separate linked tables.
  • Solves data inconsistency issue by ensuring data updates are available to all applications.
  • Solves data dependency issue as data is independent of applications using the database.

Relational Database Terminology

  • Database: A structured collection of data items and links between them for access by different applications.
  • Table: A group of similar data, with rows for instances and columns for attributes.
  • Field: A column in a table.
  • Tuple: A row in a table.
  • Entity: Anything about which data can be stored.
  • Attribute: An individual data item stored for an entity.
  • Candidate key: An attribute or set of attributes that uniquely identifies a tuple in a table.
  • Primary key: A unique identifier for a table, a special case of a candidate key.
  • Secondary key: A candidate key that is an alternative to the primary key.
  • Foreign key: A set of attributes in one table that refers to the primary key in another table.
  • Relationship: A situation where one table has a foreign key referencing a primary key in another table.
  • Referential integrity: A state where foreign key values match corresponding primary key values.
  • Index: A data structure built from columns to speed up searching.
  • Normalization: Organizing data into tables and relationships to minimize redundancy.
  • First normal form (1NF): Entities do not contain repeated groups of attributes.
  • Second normal form (2NF): Entities are in 1NF and non-key attributes depend on the primary key.
  • Third normal form (3NF): Entities are in 2NF and all non-key attributes are independent.
  • Composite key: A set of attributes forming a primary key.

Entity-Relationship (E-R) Diagrams

  • Graphical representation of a database and the relationships between entities.
  • Types of relationships: one-to-one (1:1), one-to-many (1:m), many-to-one (m:1), many-to-many (m:m).
  • Cardinality: Type of relationship with whether it is mandatory or not.

Normalization

  • Used to construct relational databases with integrity and reduced redundancy.
  • Unnormalized databases can cause issues such as data modification problems.
  • Follow the rules for normalization to avoid issues with databases.

Database Management Systems (DBMS)

  • System software manages databases.
  • Data management is the organization and maintenance of data.
  • A data dictionary contains metadata.
  • A logical schema is a data model for a specific database.
  • Access rights are permissions for database users.

DBMS Addresses File-Based Limitations

  • Solves data redundancy
  • Solves data inconsistency
  • Solves data dependency

Features of a DBMS

  • Data modelling tools, logical schema, query processor, developer interface
  • Security measures: Usernames, passwords, access rights, data encryption

Data Definition Language (DDL)

  • Used to create, modify, and remove data structures.
  • Commonly used commands: CREATE DATABASE, CREATE TABLE, ALTER TABLE and PRIMARY KEY
  • Common supported data types: CHARACTER, VARCHAR(n), BOOLEAN, INTEGER, REAL

Data Manipulation Language (DML)

  • Used to add, modify, delete, and retrieve data.
  • Commonly used commands: SELECT, FROM, WHERE, ORDER BY, GROUP BY
  • Additional DML commands: INSERT INTO, DELETE FROM, UPDATE

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

More Like This

Use Quizgecko on...
Open
Browser
Browser