Jarzin Supermarket has been in the retail (wholesale) business for the past 40 years. And over the years, the company has grown significantly. However, Jarzin has failed to greatly benefit from its growth due to the lack of a well-managed and maintained database management system that keeps the records of the business. Jarzin has approached you as a DB administrator/designer to develop their database that will capture all their business processes. Mr Banda, who has been working with the organisation for the past 25 years, has been tasked by Jarzin to see to it that you get all the business requirements. During your meeting with Mr Banda, you ask him to tell you how the business operates and what the business needs are. The following is the response Mr Banda gives you: “As a retail company, we cater for the needs of various customers. Some customers are once off customers who visit the shop, buy their desired products, pay and leave. We also have regular customers. Our regular customers are those whose information we keep within our system. Each customer has a unique identification that is used each time they buy from the shop. The customer information kept in the system is the name, their phone numbers, email address, city, postal address, etc. A customer makes payment(s) to their account. There is a unique payment identification for the payment, a date of payment and an amount paid by the customer. Each customer can make as many orders as possible, and for each order, an order number is generated, which also captures the date of order, required date when the order will be delivered, and a status of delivery (whether it was delivered, to be delivered). Each order has its own orderdetails, which are captured. The orderdetails captures the order number, product code, quantity ordered of each product, price of each item, order line number, etc. For each order, there is one order detail available. For each productline we capture the unique product line id, text description of the productline. Each productline can have an infinite amount of products. This is optional for products. The products that are part of the productline have a one to one relationship. The orderdetails for the products also have a one to one relationship. For all our products we keep the following information: product code (which is unique to each product), product name, productline, product scale, product vendor, product description, quantity in stock, price, etc. Our customers are optionally served by an one employee at a time, while our employees can serve as many customers as possible. We keep employees’ details such as the employee id, last and first name, extension, email, job title, etc. Each employee works only in one office, while our offices have many employees. Note that not all employees serve customers, because we have some who are supervisors. Please note that we have many offices within the same region, and we have offices in 6 provinces. So, our offices have a unique code, and each is their own city/region. Information such as the office phone numbers, addresses are important to us.” “This is all our business requires to be in the new database management system,” concluded Mr Banda.
Jarzin Supermarket has been in the retail (wholesale) business for the past 40 years. And over the
years, the company has grown significantly. However, Jarzin has failed to greatly benefit from its growth
due to the lack of a well-managed and maintained
records of the business.
Jarzin has approached you as a DB administrator/designer to develop their database that will capture
all their business processes. Mr Banda, who has been working with the organisation for the past 25
years, has been tasked by Jarzin to see to it that you get all the business requirements.
During your meeting with Mr Banda, you ask him to tell you how the business operates and what the
business needs are. The following is the response Mr Banda gives you:
“As a retail company, we cater for the needs of various customers. Some customers are once off
customers who visit the shop, buy their desired products, pay and leave. We also have regular
customers. Our regular customers are those whose information we keep within our system. Each
customer has a unique identification that is used each time they buy from the shop. The customer
information kept in the system is the name, their phone numbers, email address, city, postal address,
etc. A customer makes payment(s) to their account. There is a unique payment identification for the
payment, a date of payment and an amount paid by the customer.
Each customer can make as many orders as possible, and for each order, an order number is
generated, which also captures the date of order, required date when the order will be delivered, and a
status of delivery (whether it was delivered, to be delivered). Each order has its own orderdetails, which
are captured. The orderdetails captures the order number, product code, quantity ordered of each
product, price of each item, order line number, etc. For each order, there is one order detail available.
For each productline we capture the unique product line id, text description of the productline. Each
productline can have an infinite amount of products. This is optional for products. The products that are
part of the productline have a one to one relationship. The orderdetails for the products also have a one
to one relationship.
For all our products we keep the following information: product code (which is unique to each product),
product name, productline, product scale, product vendor, product description, quantity in stock, price,
etc.
Our customers are optionally served by an one employee at a time, while our employees can serve as
many customers as possible. We keep employees’ details such as the employee id, last and first name,
extension, email, job title, etc. Each employee works only in one office, while our offices have many
employees. Note that not all employees serve customers, because we have some who are supervisors.
Please note that we have many offices within the same region, and we have offices in 6 provinces. So,
our offices have a unique code, and each is their own city/region. Information such as the office phone
numbers, addresses are important to us.”
“This is all our business requires to be in the new database management system,” concluded Mr
Banda.
Step by step
Solved in 5 steps
