Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

database design report > Database report > Flashcards

Database report Flashcards

oral exam revision (64 cards)

Start Studying
1
Q

What is the core business problem faced by Gelatiee?

A

Excessive waste of gelato due to a lack of real-time visibility into operations and disconnect between shop activities and management decisions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is the primary goal of the information system being developed for Gelatiee?

A

To create a relational database system that monitors stock levels, identifies near-expiration products, logs dropped scoops, tracks expired products, and aids in data-driven decisions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Why is a database system preferred over spreadsheets for Gelatiee?

A

A database system supports scalability, structured data storage, and complex insights extraction via queries.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are some complementary assets necessary for the database system to be effective?

A
  • Managerial support
  • Adjusted organizational processes
  • Employee training
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

True or False: The database system directly targets customer intimacy as its main goal.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How does the database system contribute to customer experience at Gelatiee?

A

By identifying near-expiration products for discounts and providing data on best-selling flavors for employee recommendations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the role of the Employee (Server) in the database system?

A

To log waste, check for near-expiry products, and provide accurate billing and recommendations to customers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Fill in the blank: The store manager uses the system to track total sales made by __________.

A

[employees]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What does the Inventory Manager need to monitor regarding waste?

A

To track waste volume in liters, ensure accurate stock levels, and adjust ordering based on waste data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the central entities in Gelatiee’s database design?

A
  • PRODUCTS
  • EMPLOYEE
  • RECEIPT
  • RECEIPTLINE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What type of relationships are primarily implemented in Gelatiee’s database model?

A

1:N relationships.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does the WASTELOG entity document?

A

Product loss, including the amount wasted, reason, and responsible employee.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

As a customer, what does Sofie value when choosing a gelato shop?

A
  • Discounts on near-expiry gelato
  • Knowledgeable staff
  • Sustainability efforts
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

True or False: The database system allows employees to log dropped scoops without any training.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is one of the key challenges in the database system for Gelatiee?

A

Reliance on staff input for logging dropped scoops and expired products.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

How does Gelatiee ensure that waste-logging is easy for employees?

A

By aligning waste-logging with existing daily routines.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the importance of the database in achieving operational excellence for Gelatiee?

A

It helps minimize waste, optimize restocking, and train staff based on data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What kind of training is essential for employees in relation to the database system?

A

Training on the importance of the system and how to use it correctly.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

List some user stories from the perspective of a Store Manager.

A
  • Discount near-expiry products automatically
  • Monitor waste caused by employees
  • Track total sales and waste correlation
  • View total waste due to expiry in liters
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What does the database system provide to the Store Manager for decision-making?

A

Quick, data-informed insights to reduce waste and enhance operations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Fill in the blank: The __________ allows for quick adjustments to stock orders based on waste data.

A

[inventory manager]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is the primary relationship pattern implemented in the data model?

A

1:N relationship patterns

Example: One employee can issue many receipts, and each receipt can include multiple receipt lines.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the purpose of the RECIEPTLINE entity?

A

To maintain data normalization and avoid redundancy in many-to-many relationships.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What types of primary keys were defined for each entity?

A

Unique IDs, such as P_ID for products and R_ID for receipts.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What is a composite primary key?
A primary key made up of two or more attributes, such as (R_ID, P_ID) in the RECEIPTLINE table.
26
What is the significance of normalization in database design?
To ensure that each attribute in a table is determined by its primary key and to avoid redundancy.
27
What does First Normal Form (1NF) require?
All attributes must be atomic, meaning each field contains only indivisible values.
28
What does Second Normal Form (2NF) eliminate?
Partial dependencies; all non-key attributes must be fully dependent on the entire primary key.
29
What are transitive dependencies that Third Normal Form (3NF) avoids?
Non-key attributes that depend on other non-key attributes.
30
What is the purpose of the WASTELOG and STOCKLOG tables?
To allow accurate and up-to-date tracking of past inventory activities.
31
How are cascading updates and deletes handled in the database design?
They are not implemented; deletions are handled manually.
32
What is the primary purpose of business rules in the database?
To define how data is structured and managed, ensuring consistency.
33
What is the standard measurement for one scoop of ice cream?
0.1 litres.
34
What must a receipt contain according to the sales and receipts business rule?
At least one line (i.e., at least one product sold).
35
What is the primary key for the EMPLOYEE table?
E_ID.
36
What SQL datatype is used for the primary key E_ID?
INTEGER.
37
What is the purpose of the AUTO_INCREMENT feature in SQL?
To generate a unique number ID for each record automatically.
38
What does the RECEIPTLINE table resolve?
The many-to-many (N:M) relationship between RECEIPT and PRODUCTS.
39
What are the attributes of the WASTELOG table?
WL_ID, E_ID, P_ID, WL_Reason.
40
What does the ON DELETE SET NULL action do in the STOCKLOG table?
Sets the inventory ID in the log to NULL to preserve data when refreshing.
41
What is an SQL view?
A virtual table constructed from other tables or views, with no data of its own.
42
What does the first view in the database help staff identify?
Expired products that need to be discarded.
43
What SQL command is used to combine information from two tables in a view?
JOIN.
44
What does the second view help employees identify?
Popular flavors based on sales data.
45
What function is used to calculate total scoops sold for each product in the view?
SUM.
46
What is a trigger in the context of a database?
An automatic response to specific actions performed on a table.
47
What does the CalculateWasteLiters trigger do?
Automatically calculates the waste in liters before a new row is inserted in the WASTELOG table.
48
What is the purpose of the CalculateWasteLiters trigger?
Automatically calculates the waste in liters before a new row is inserted in the WASTELOG table ## Footnote This trigger helps avoid manual calculations and prevents errors, ensuring accuracy.
49
What does the UpdateInventoryBeforeDroppedLogged trigger do?
Subtracts the amount of waste in liters from the I_CurrentStockLiter column when waste reason is 'Dropped' ## Footnote This trigger executes before a new row is inserted into the WASTELOG table.
50
What is the function of the UpdateInventoryAfterExpiredLogged trigger?
Updates the inventory stock when waste reason is 'Expired' after a new row is inserted in the WASTELOG table ## Footnote This ensures accurate stock levels based on logged waste.
51
How does the UpdateReceiptTotal trigger work?
Automatically updates the total price in the receipt when a new item is added in the RECEIPTLINE table ## Footnote It calculates total price by multiplying price per scoop by quantity.
52
What does the ApplyDiscountToReceiptLine trigger do?
Applies a discount to products near expiry when added to a receipt if certain conditions are met ## Footnote Sets RL_DiscountApplied to 20% if conditions are satisfied.
53
What is the role of the UpdateInventoryAfterSale trigger?
Automatically updates inventory levels after a sale is made by adjusting stock quantities ## Footnote Increases I_StockOutScoop and I_StockOutLiter, decreases I_CurrentStockLiter.
54
What does the UpdateNearExpiryStatus trigger check?
Updates the I_NearExpiry status based on how close the product's expiry date is to the current date ## Footnote Sets I_NearExpiry to TRUE if expiry date is within 2 days.
55
What is a query in the context of a database?
A request made to the database to perform a specific operation or retrieve data ## Footnote Queries can be used to track waste, sales, and employee performance.
56
What does the query to track expired waste per each product help with?
Monitors how much gelato is being wasted due to expiry for each product ## Footnote Aids in identifying products that frequently expire for better ordering decisions.
57
What is the purpose of the query that tracks waste by employee?
Allows the store manager to implement training based on employee waste behavior ## Footnote Utilizes LEFT JOIN to include all employees, even those with zero waste.
58
What information does the query to track total sales made by each employee provide?
Shows number of sales, receipts handled, total money earned, and total scoops sold by each employee ## Footnote Helps in evaluating employee performance against waste.
59
What is the significance of connecting a database with Python?
Enables automation of tasks and data analysis efficiently ## Footnote Uses libraries like 'mariadb' for database interaction and 'getpass' for secure password entry.
60
What steps are involved in connecting Python to a SQL database?
Include importing libraries, establishing a connection, creating a cursor, executing queries, and closing connections ## Footnote Ensures proper session termination and resource management.
61
What key insights were gained from the design and implementation of the database system?
Understanding of real-world database development principles and challenges, focusing on sustainability and inventory tracking ## Footnote The project highlighted the balance between technical design and practical needs.
62
What future improvements were suggested for the database project?
Inclusion of customer loyalty programs, better supplier data tracking, and real-time timestamp usage for inventory entries ## Footnote Aimed at enhancing waste reduction and inventory management.
63
What technical challenge was encountered during the project?
Inability to use user-defined functions (UDFs) on the SQL server, leading to reliance on views and queries ## Footnote This limitation prompted creative problem-solving within technical constraints.
64
True or False: The UpdateInventoryAfterSale trigger is executed before a sale is recorded.
False ## Footnote It executes after an insert operation on the RECEIPTLINE table.

database design report (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions