Question

1. The Cool Clothing Company is developing a new computer order processing system to process mail order sales of its products. Customers send in their orders, by email, together with a credit card payment. Availability of the ordered goods is then checked in an inventory file; then the validity of the credit card payment is checked online to customer's bank. If these two checks are successful, a dispatch paid order is sent to the customer and warehouse. The warehouse is a third party company responsible for delivery of goods to customers. If the goods ordered are out of stock, then the order is placed on a waiting list and reconsidered at the end of the week. If the bank does not give the credit card authorization, then the order is sent back to the customer with an explanatory note. From the above description, draw a diagram showing the flow of input information (and/or data) and output information (and/or data) to and from processes within this order processing system and any external environmental elements that interact with this order processing system. In this diagram, you must identify the following: a. internal processes inside this ordering system (15 marks) b. database stores, external environmental elements that interact with any of the processes identified in (a) (6 marks) c. input and output information (and/or data) to and from each of the processes, database stores and external environmental elements identified in (a) and (b) (9 marks) (Hint: there are three environmental elements that interact with this order processing system, and six main processes in this order processing system) 2. In relational databases, each table must have a primary key. Describe what a primary key is and its purpose. Illustrate with examples in your answers. (4 marks) 3. In relational databases, foreign keys may or may not appear in a table. Describe what a foreign key is and its purpose. (4 marks)

Answer 1

Que.1) The data flow diagram of an ordering system for the Cool Clothing company is shown in the attached image. a) Ans:- There are 6 internal processes are present in the ordering system of Cool Clothing company, that are -

START Lorder customer - via Email (3/P) Inventory file Abailibility of goods Enhet (3/P) 7 Payment credit card stock?) Explainatory Note [NOT [YES YES NO Both satisfied ordered order cancelled Toispatch order Horder msg. to customer Warehouse - Yes In Stock & I yes/ pale I waiting listke NOI walion order I placed recheked weekly CEND Round END

1- Taking an order and setting a payment gateway is the first process that is done by the system.

2- Checking the availability of goods in inventory files will be the next process of a system.

3- Checking the validity of a credit card of the customers for payment of goods and taking the actions according to its results is one of the processes of the system.

4- Dispatching order is the process done after checking the stock of the goods and the validity of the payment gateway.

5- The next process is done by data warehouse for checking the stock and taking actions according to that.

6- After the fulfillment of the above processes, the last process is to place an order of the customer.

b) Ans:- In this system three databases are used for placing an order

1- Iventary files - These are the file database used for storing the files.

2- Bank's database - It is the Bank's database of a customer for checking the validity.

3- Data Warehouse - It is a large database or repository where we can store large amounts of data in an organized manner.

c) Ans:- In the first process, the input is an email to the company for placing an order and its result is to start the ordering process.

- In the second process for checking the availability of goods, the input is the inventory files.

- In the third process for checking the validity of a credit card, the input is the bank's database.

- In the fifth process, the input is a data warehouse for checking the stock and it gives an output a list of available goods.

The databases used are Inventory files, Bank's database, and Data warehouse.

There are three external environmental conditions which affect the processes are as follows -

Locational factors- These show the remote areas where the order cannot be dispatched.

Market conditions - These show the conditions of the market such as input and resource availability, users, and competitions.

Public policies - These show the government support, regulation, and tax system which can affect the system.

Que 2) Ans- A primary key is a table column that serves a special purpose. Each database table needs a primary key because it ensures row-level accessibility and it shows the uniqueness of a table. The main purpose of a primary key is to implement a relationship between two tables in a relational database. the primary key is used for a foreign key as a reference. For Example: In the student table where attributes are student Id, subjects and courses. In student table, the student Id is uniquely defines a table so it is a primary key for a student table.

Que 3) Ans- A foreign key is basically used for joining two tables. A foreign key is a column that references a column most often the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In relational databases, the foreign key may or may not be present.