This is the project of Open Information Systems and it is under supervision of Dr. Bas Ketsman, Mr. Tim Baccaert and Mr. Samuel Ngugi Ndung'u.
As you can see, there are eight tables available in this part of the database. The first table contains the personal information of the customers, and Email is the primary key. There is no field for national id numbers because this design should replace amazon, which is an international company. This table also has a foreign key that is Delivery_Kind, and it is from the third table (Delivery_servise_types). This table is accessible and editable by the management and the customer, and I should also mention that each customer's information is only available for him/herself and not any other user. Also, any change of keys should be applied to other tables as well. Moreover, I should mention that all the fields are string types except the Birth_date column (date type). So editing and changing the personal data of users is possible for them. Also, the system can give a price for delivery services based on the type of delivery service and the address.
The second table is the table of addresses. All the fields are a Primary key, and Email is a foreign key and connected to the first table. This table contains the addresses of customers. Each customer can have many addresses. This table is accessible and editable by the management and the customer, and I should also mention that each customer's information is only available for him/ herself and not any other user. Also, any change of keys should be applied to other tables as well. Furthermore, I should mention that all the fields are string types. So editing address and location and also adding new addresses is possible for the users.
The third table is the table Delivery_servise_types, and it contains the types of delivery. The primary key is Delivery_Kind, which is the name of the type of delivery. This table is accessible and editable from the management, and it is only viewable for customers. Moreover, I should also mention that each customer's information is only available for him/her self and not any other user. Also, any change of keys should be applied to other tables as well. Furthermore, I should mention that Delivery_Kind is a string, Price is a float number, and Delivery_Duration is time. This table is also editable and viewable for Warehouses, Suppliers, and Delivery Services. So editing and adding new delivery types will be available for employees in delivery services and also other related departments.
The fourth table is the table of Customer_Receipt_connector, and it only connects Receipt_information and Customers_information. Email is a foreign key, and it is a connecter to the first table. Moreover, Receipt_ID is a foreign key connected to the Receipt_information table. All the columns are a primary key. This table is accessible and editable from the management, and it is only viewable for customers. Furthermore, I should also mention that each customer's information is only available for him/her self and not any other user. Also, any change of keys should be applied to other tables as well. And I should mention that all the fields are string type. So it would be possible for users to have access to all of their receipts.
The fifth table is Receipt_information, and it contains the general information of the receipts. The Receipt_ID id is the primary key, and Delivery_Status and Payment_Status are foreign keys connecting to tables Delivery_Status_description and Payment_Status_description. This table is accessible and editable from the management, and it is editable for customers in columns Delivery_status, and the rest of the table is only viewable for the costumer, and I should also mention that the information of each customer is only available for him/her self and not any other user. Also, any change of keys should be applied to other tables as well. And I should mention that all the fields are string types except the Date column (date type). This table is also editable and viewable for Warehouses, Suppliers, Products, and Delivery Services. So customers can access the status of their different purchases and pay the price if they did not do it already and change the delivery service to something else.
The sixth table is the Goods_in_receipt table for having the history of purchases. Receipt_ID and Good_ID are primary keys, and Receipt_ID is also a foreign key connecting to the Receipt_information table. There is also Good_ID that is a foreign key to connecting to database of goods and products. This table is accessible and editable from the management, and it is only viewable for customers. Moreover, I should also mention that each customer's information is only available for him/her self and not any other user. Also, any change of keys should be applied to other tables as well. And I should mention that all the fields are string type except the price column (float type) and number (int type). This table is also editable and viewable for Suppliers and Products. So with the help of this table and the Receipt_information table, it is possible to calculate the price of all receipts.
The last two tables are only for more information about the status of payment and delivery. Delivery_Status and Payment_Status are the primary keys in these tables. These tables are accessible and editable from the management, and it is only viewable for customers. Also, any change of keys should be applied to other tables as well. And I should mention that all the fields are string types. Delivery_Status_description table is also editable and viewable for Delivery Services, and Payment_Status_description able is also editable and viewable for Warehouses.