p.2
Database Design Principles
What course are the presentation slides adapted from?
CSCI235 – Database Systems.
p.3
Data Redundancy Issues
What is a key problem associated with using one big table?
It can lead to difficulties in managing and querying data efficiently.
p.14
Data Redundancy Issues
Why is it not advisable to use one big table in database design?
To avoid redundancy and maintain data integrity.
p.11
Primary and Foreign Keys
What does the foreign key cNumber in the ORDERS table signify?
It establishes a relationship with the CUSTOMER table.
p.20
Functional Dependency
What is the function representation for the relationship between cNumber and lName?
f: domain(cNumber) → domain(lName).
p.17
Functional Dependency
What does it mean if a value in column A is x and a value in column B is always y?
It means every value x in column A is associated with only one value y in column B.
p.24
Data Redundancy Issues
What are update anomalies?
Problems that arise when data is modified in a database, leading to inconsistencies.
p.20
Functional Dependency
What does it mean if every value in column oNumber is associated with only one value in column oDate?
It indicates a functional dependency between oNumber and oDate.
p.20
Functional Dependency
What is the function representation for the relationship between oNumber and oDate?
f: domain(oNumber) → domain(oDate).
p.5
Primary and Foreign Keys
What foreign key is present in the ORDERS table?
cNumber, which references CUSTOMER(cNumber).
p.19
Functional Dependency
If every value in column cNumber is associated with only one value in column fName, what does this represent?
It represents a function f : domain(cNumber) → domain(fName).
p.18
Functional Dependency
What is the definition of functional dependency in the context of databases?
It is a relationship where one attribute uniquely determines another attribute.
p.6
Relational Schema Design
What does the CUSTOMER table represent?
It represents customer orders and their details.
p.20
Functional Dependency
What does it mean if every value in column cNumber is associated with only one value in column lName?
It indicates a functional dependency between cNumber and lName.
p.24
Normalization in Databases
What is the purpose of normalization in database design?
To reduce data redundancy and improve data integrity.
p.11
Primary and Foreign Keys
What is the foreign key in the ORDERS table?
cNumber, which references CUSTOMER(cNumber).
p.8
Data Redundancy Issues
What is the main issue with using one big table for customer orders?
Redundant data, as customer information is repeated for each item in the orders.
What is a data dependency example involving COLUMN_1 and COLUMN_2?
If COLUMN_1 is green, then COLUMN_2 is red.
p.4
Relational Schema Design
What does an order consist of?
Lines that contain information about ordered items.
p.9
Data Redundancy Issues
What is the main issue with using one big table for customer orders?
Order number and date are repeated for each item, leading to redundancy.
p.4
Data Redundancy Issues
Why might one consider not using a single table for this database?
To avoid redundancy and maintain data integrity by separating different entities.
p.8
Data Redundancy Issues
What is the total number of different items purchased by James Bond?
Seven items across two orders.
p.9
Multi-table vs. Single-table Design
What is a better design approach than using one big table for orders?
Using multiple tables to separate customers, orders, and order items.
p.7
Relational Schema Design
What is the significance of 'oDate' in the order data?
It indicates the date the order was placed.
p.7
Relational Schema Design
What does 'lNum' represent in the order details?
Line number of the item in the order.
p.3
Data Redundancy Issues
What is the main reason for not using one big table in database design?
To avoid issues related to data redundancy and maintainability.
p.17
Functional Dependency
Can a customer number in column cNumber be associated with multiple first names in column fName?
No, every customer number is associated with only one first name.
p.10
Data Redundancy Issues
What is the advantage of a multi-table design?
It avoids problems associated with data redundancy and inconsistency.
p.11
Data Redundancy Issues
Why is it suggested not to use ONE BIG TABLE in database design?
To avoid data redundancy and improve data integrity.
p.19
Functional Dependency
What is the notation used to represent the function mapping from column A to column B?
f : domain(A) → domain(B)
p.18
Functional Dependency
What can be said about the relationship between order number and item name?
An order number can be associated with only one item name.
p.21
Functional Dependency
What does the notation A → B represent in a relational table?
It denotes a functional dependency where A functionally determines B.
What is the relationship between cNumber and fName/lName?
If cNumber = 7, then fName = James and lName = Bond.
p.6
Data Redundancy Issues
What is the significance of having multiple entries for the same customer in the CUSTOMER table?
It allows tracking of multiple orders and items for each customer.
p.23
Functional Dependency
What is functional dependency?
A special kind of data dependency reflecting real-world consistency constraints.
p.2
Database Design Principles
Which university is associated with the lecture slides?
University of Wollongong, Australia.
p.18
Functional Dependency
What is the relationship between order number (oNumber) and order date (oDate)?
Every order number is associated with only one order date.
What does cNumber represent in the data?
It represents the customer number.
p.21
Functional Dependency
What is a functional dependency in the context of a relational table?
It is a relationship where the value of one column (A) determines the value of another column (B).
p.5
Data Redundancy Issues
Why is it beneficial to use multiple tables instead of one big table?
To reduce data redundancy and improve data integrity.
p.21
Functional Dependency
When is a functional dependency considered valid in a relational table?
When A functionally determines B in that table.
How can data dependencies be represented?
As a separate relational table.
p.22
Functional Dependency
What does the functional dependency oNumber → oDate signify?
oNumber determines oDate.
p.13
Data Redundancy Issues
What causes redundancies in an incorrectly designed relational table?
Improper handling of data dependencies.
p.4
Relational Schema Design
What information is included in an order?
A unique order number and order date.
p.8
Data Redundancy Issues
What does the example illustrate about customer orders?
Each order consists of several lines, leading to data duplication.
p.4
Relational Schema Design
What information is contained in a line of an order?
Name of ordered item, price per single item, and total number of ordered items.
What can be inferred for any color x in COLUMN_1?
If COLUMN_1 is x, then COLUMN_2 is y.
p.12
Data Redundancy Issues
Why is it not advisable to use ONE BIG TABLE in database design?
To avoid data redundancy and improve data integrity.
p.24
Normalization in Databases
How does normalization support database design?
By organizing data to minimize redundancy and dependency.
p.23
Functional Dependency
How can functional dependencies assist in database design?
They can be used to design a database.
p.19
Functional Dependency
What does it mean if every value in column A is associated with only one value in column B?
It means that columns A and B represent a function that maps values in column A to values in column B.
p.10
Data Redundancy Issues
Why is a multi-table design preferred over a single big table?
To prevent data redundancy and maintain data integrity.
p.7
Data Redundancy Issues
What is the main problem with using one big table for customer orders?
It leads to data redundancy and inconsistency.
What is the relationship between COLUMN_1 being orange and COLUMN_2?
If COLUMN_1 is orange, then COLUMN_2 is red.
What is the significance of having a customer number in the data?
It allows for identifying unique customers and their associated information.
p.12
Relational Schema Design
What are the attributes of the LINE table?
oNumber, lNumber, item, price, total
How many items are listed under order number 7?
3 items (bolt, screw, nut).
p.23
Functional Dependency
How can functional dependencies be used in relation to data?
To describe the semantics (meaning) of data.
p.23
Functional Dependency
What role do functional dependencies play in relational schema construction?
They help determine whether a relational schema is constructed correctly.
p.24
Data Redundancy Issues
What are data redundancies?
Unnecessary duplication of data within a database.
p.8
Data Redundancy Issues
What information is repeated for each item in the orders?
Customer number, first name, and last name.
p.8
Data Redundancy Issues
What is the consequence of having repeated customer information in a database?
Increased storage requirements and potential for inconsistencies.
p.9
Data Redundancy Issues
What information is repeated in the one big table approach?
Order number and order date.
What is the relationship between oNumber and oDate?
If oNumber = x, then oDate = y.
p.12
Primary and Foreign Keys
What is the foreign key in the LINE table?
oNumber REFERENCES ORDERS(oNumber)
p.10
Relational Schema Design
What are the attributes of the CUSTOMER table?
cNumber, firstName, lastName.
p.4
Relational Schema Design
What unique identifier describes a customer in the database?
A unique customer number.
p.17
Functional Dependency
What is an example of functional dependency in a database?
Every customer number in column cNumber is associated with only one last name in column lName.
p.17
Functional Dependency
What does functional dependency imply about the relationship between two columns?
It implies a one-to-one relationship where one value in the first column determines a unique value in the second column.
What are the first and last names associated with cNumber 7?
First name: James, Last name: Bond.
p.9
Data Redundancy Issues
Why is it inefficient to store customer and order information in one table?
It leads to unnecessary duplication of data for each item in an order.
p.6
Primary and Foreign Keys
What is the primary key for the CUSTOMER table?
(cNumber, oNumber, lNumber)
p.18
Functional Dependency
Can an item name in the item column be associated with multiple order numbers?
Yes, an item name can be associated with many order numbers.
p.7
Data Redundancy Issues
What information is repeated for each order line in the provided data?
Customer number, first name, last name, and order number.
p.16
Data Redundancy Issues
Why might a single large table not be ideal for this data?
Data dependencies can lead to redundancy and inconsistency.
p.7
Data Redundancy Issues
Why is it inefficient to store multiple order lines in a single table?
It can lead to increased storage requirements and complex queries.
p.6
Data Redundancy Issues
Why is it not advisable to use one big table for the CUSTOMER data?
It can lead to data redundancy and difficulties in managing relationships.
p.6
Relational Schema Design
What are the key attributes in the CUSTOMER table?
cNumber, firstName, lastName, oNumber, oDate, lNumber, item, price, totalQty.
p.5
Primary and Foreign Keys
What foreign key is present in the LINE table?
oNumber, which references ORDERS(oNumber).
p.9
Multi-table vs. Single-table Design
What does the example of James Bond's orders illustrate?
The problem of data redundancy in a single-table design.
p.6
Relational Schema Design
What type of data does the CUSTOMER table store?
Customer information, order details, and item specifics.
p.12
Relational Schema Design
What does the LINE table represent?
Details of items in an order including item, price, and total.
p.7
Normalization in Databases
What is the purpose of having separate lines for each item in an order?
To accurately represent the quantity and details of each item ordered.
p.15
Data Redundancy Issues
Why might a single large table not be ideal for data organization?
It can lead to data redundancy and complicate data management.
