Tulips Database Design ERD Crows Foot Notation & Normalization | Government of Cicada Case Study.TXT

Tulips Database Design ERD Crows Foot Notation & Normalization | Government of Cicada Case Study

Recently it was announced by the Government of Cicada that Tulips would be legalized for consumers and businesses. Your company has been hired by the Government of Cicada to design the database to be used for monitoring this new industry. As the resident database designer at your company, you have been assigned to interviewing Mr. Colin Mochrie, the Government of Cicada’s representative in charge of the information infrastructure projects surrounding the new legal Tulips industry.

  1. Based on the transcript below, develop the complete ERD for this database with all relevant labels.

  2. Using the attached excel file, perform a full normalization of the sample data with all steps and relevant labels. Note: the flat file may be incomplete, so not all the data is represented in it. 

Interview Transcript

Nice to meet you Mr. Mochrie, thank you for taking the time to do this interview with me. It will really clarify things and help gain an understanding of the functional requirements of this database, allowing us to properly design the database schema prior to the actual implementation.

“Hi, thanks, yes I am happy to help out. Whatever I can do to help expedite the process, we want to have the database up and running very soon.” Great, then to begin, can you tell me a bit about what you want to use the database for? I understand it is to be used for monitoring businesses during the legislative changes, but what precisely are you trying to keep track of?

“Certainly. So for starters, we want to have an online catalogue of products that customers can purchase from using an online portal. The products include all kinds of tulips and related paraphernalia. Our online portal will need to be able to query the database to access the product lists. Product information should include the name of the product, a brief description, colour, smell, taste, and wholesale price.”

Interesting, so tell me more about these products. Where do you get them? How do they finally get to the end user?

“So the tulips are provided by suppliers. Some suppliers provide many different tulips, others specialize in very high quality tulips and have a lower selection. Supplier information should include the name, address, contact number, and supplier rating. Distributors acquire wholesale tulips from the suppliers and then provide them to retail outlets. For distributors we want to know the name, address, contact number, and distributor rating. Customer orders are ultimately fulfilled by those retail outlets. The orders should record the order date, quantity and retail price. For customers, we want to know the name, address, contact number, username, password and birthday.”

Ok, can you give me an example of some question you might want to use the database to answer?

“Interesting question. One of the main things we want to know is for a given product, which retailers are currently selling it, and which distributors provided it, and which supplier originally produced it. This information is crucial in monitoring the supply chain, in case of product withdrawals or safety hazards.”

Great. This is a lot of information already. Is there anything else you think is pertinent regarding the customers and retailers? I’ve heard some provinces of Cicada are planning to allow private entrepreneurs to open retail outlets, while others are restricting retail licenses to government branches. Can you explain a bit more about that?

“You’re correct, some provinces intend to allow private businesses and others are only allowing retail licenses to be given to Government outlets within the existing infrastructure of other legalized vices. We treat both as retailers in our database but there is some information we need exclusively for each. For retailers in general, we’d like to know name, address and contact number as well as the retail license number, date of issuance, date for re-appraisal, and the name of the individual who signed for the license. For private businesses we want to know the date of incorporation, name of CEO, business address, etc. As for government retailers, we want to know the name of the current manager, contact information like phone number, and address.”

Fascinating. Okay I think we’ve made great progress, but there is something else I was curious about. What do you do about the medicinal patients who had been accessing tulips for medicinal purposes prior to this legislative change? Do you intend to incorporate their information into this database?

“Very perceptive. Indeed who do want to include information about customers who may be medicinal users. When a customer registers in our system, they can apply to be a Premium user, a medicinal user, or they may be neither. Medicinal users are given priority in the events of pre-ordering or stock shortages. It’s also important to verify medicinal users, so we want to have a record of their prescription and the details on the doctor who provided it. Some doctors specialize in the medicinal benefits of tulips and so make many prescriptions for multiple users. Medicinal users information should include the medical condition they are diagnosed with and the diagnosis date. Premium users are given priority access to new varieties of tulips. For premium users, we want to know the premium subscription start date.” Okay, I think I’ve gathered enough information to begin the design process. Again, thank you Mr. Mochrie for your time.

“My pleasure. Looking forward to seeing what you come up with.”

As you place the order, you'll be getting the project in some hours on your email address. So stay calm to get 100% project :)

Meanwhile, if you've any questions please feel free to contact us anytime.

- via WhatsApp: +92-324-7042178
- via email: [email protected]
- via Fiverr: www.fiverr.com/codelogix
Powered by