Enqurious logo
Liked the masterclass! Give it a shot for free
Get access

Rajiv, the go to guy for SQL in the company has been promoted to a Database Administrator role. In this role, he not only privileges to read and execute SQL queries, but also has the privileges to create, modify, delete or drop tables. 

 

One thing he notice that the company is still running their business on paper based forms to accept customer orders, manage returns etc.

 

The company CTO had a conversation with him and has sought suggestion from him about how to digitize the company's business?

 

The 1st challenge he has got is how to digitize the core business process which is : Order Management

The Order Management Process involves primarily the following business flows :

👉 Raising Purchase Order/Customer Order

👉 Raising Invoice

👉 Raising Supply Order Form (to Vendors)

👉 Raising Return Order

 

Let's look at how does the Purchase Order Form used by GlobalMart looks like :

 

Rajiv has the following ask from the CTO :

 

 

Come up with a feasible data storage solution which should fulfill the below requirements :

  • Data should not be lost in any transactions
  • No inconsistent data
  • Highest level of Data Integrity
  • Highest levels of data quality (avoid data duplication, wrong data etc. )

Now the challenge :

✔️ Suggest a suitable database to cater to the client needs. State the reasons why you went ahead with your choice

✔️ Design a robust data model to digitize the business

✔️ Convert the ER Model you designed to physical tables by writing DDL scripts for each entity you identified (For this you'll need to connect to your local database)

 

Here is the current way of storing purchase order data by GlobalMart procurement Team. Use this as the starter for this task and apply your E-R Modelling skills to improve upon the same to come up with the most relevant data model to serve this requirement. 

 

Points to keep in mind while you design the E-R Model :

  • Take care of correct data types
  • Decide the correct levels of normalization
  • Design the correct set of constraints :
    • Primary Key
    • Unique
    • Not Null
    • Check
    • Default
    • Foreign Key etc.
  • Design the correct cardinality of relationships between entities

Ensure you are connected to the database as mentioned here

Not sure of how to approach this problem? May be you need a refresher of the below topics and re-attempt the task : 

  • What is an E-R model?
  • How does E-R model help represent and model the relationships between entities in a database?
  • How to design an E-R model?

Check out below two videos to help you grasp knowledge of the topics mentioned above. You can also experiment with this script to quickly learn about the idea of constraints. 

 

  • Ensure that you are connected to the correct database. 

Note : The connection details are available on request. Hit Get Access to start the process

  • Ensure that every table you create is prefixed by your name. Ex. if the table to be created as employee_records and your name is John Doe you should create it as john_doe_employee_records.

Upload a neat E-R Diagram created using Excel or draw.io software (upload the exported png file)
Upload well commented .sql file having the required DDL scripts to be used to create the tables
Mark the statements which are true for Foreign Keys
Foreign keys cannot be blank
Foreign keys can be blank
Foreign key must connect to some primary key
Foreign key can have values outside the primary key it is connecting to
Which of the following constraints help tackle data integrity problems?
PRIMARY KEY CONSTRAINT
FOREIGN KEY CONSTRAINT
DEFAULT CONSTRAINT
NOT NULL CONSTRAINT
Which of the following is true for Primary key constraint?
The column with primary key constraint allows NULL values
The column with primary key constraint does not allow duplicate values
The column with primary key always connects to a foreign key in another table
There should be at least one primary key in a table

You have created orders table where the definition of order_status column looks like below : 

order_status varchar(10) NOT NULL

Now, you are trying to insert the below record in the table : 

 

CA-2014-100006DK-13375Standard ClassVEN04unavailable6-19-18 5:33 PM6-19-18 6:03 PM6-20-18 10:46 AM6-27-18 7:58 PM25-07-2018
What would be the outcome of the above?
Executes without an error
Fails with syntax error
Fails with data truncation error
Executes successfully with data truncation warning
For table orders, you need to add referential integrity constraint (foreign key constraint) for vendor_id. This would mean that for any given order there needs to an associated vendor. Complete the below statement which would add the required constraint on the orders table : CONSTRAINT FK_OrderVendor FOREIGN KEY (__________) REFERENCES _________(_______)
vendor_id, transactions, vendor_id
vendor_id, vendors, vendor_id
vendor_id, customers, vendor_id
order_id, orders, vendor_id
You have employees salary table. You want to ensure nobody by mistake enters a 0 or negative salary value. Which of following constraint will help you out here?
CHECK
NOT NULL
DEFAULT
UNIQUE
You have two tables. Classroom and Students. The classroom table has following classroom IDs : CL101, CL102 and CL103 The students table has reference to classroom CL104 which is not there is Classroom table. Which data quality issue does this belong to?
COMPLETENESS
DUPLICATION
INTEGRITY
CONSISTENCY
You are designing the customers table where gender by default is set as M. For entries of customers where gender is missing, database would be default add M to the gender column. select the code which will help you correctly apply this constraint in the customers table
Gender char(1) DEFAULT 'Male'
Gender char(1), CHECK (Gender='M')
Gender char(1) DEFAULT 'M'
Gender char(1), CHECK (Gender='Male')