Project proposal

In this project I will be constructing a database management system for SKJ business store. This is an online and in-store business.

Queries

The database for this system will be able to perform some queries to extract the information. Here are the few examples
- Find the order placed by certain customer on certain date
- Find the manufacturing country of certain item

Submit the artifacts of the logical and physical design for your project; in particular, submit the following items:

1) A complete Entity- Relational diagram.

This is the ER diagram updated from Task1. Please make sure your ERD has clearly named entities, relationships, participation and cardinality constraints, keys for each entity unless it is a weak entity. Please try to minimize line crossings and remove redundant attribute, entities or relationships. If you do not use standard notation, please provide a legend.

2) All tables including their primary keys, foreign keys, unique constraints, domain constraints, attribute data types for your chosen DBMS You can list the tables, their attributes and the constraints as you have done in the Database Design course. Make sure your tables can be obtained from the ERD - unless you have done normalization or denomalization, in which case you need to explain how the table is obtained.

3) drafts of SQL for each query in your list of typical queries of the earlier milestone. Make sure you can make some estimates of workload, disk space requirement and consider which indexes and of which type you will be suggesting.

For estimate of workload, you can reasonably guess how frequent the queries/update operations are submitted. For estimate of disk space, you can first estimate the number of records in each table, based onwhich and the attribute types, make a simple calculation of space for each table. For this assignment you can ignore the file headers and separator characters. You can also ignore the indexes and views (note that in practice you must include index structures, materialized views and you need to know the data structures in your DBMS storage engine to have a more accurate estimate). The choice of indexes depends on your workload, the query conditions, as well as the table size, and attribute domain, as you have practiced before. Please provide a justification why each index is chosen.

4) Justification why a table is not in 3NF if such table exists.

If all your tables are all in 3NF, then you can ignore this step. It is perfectly fine to have all tables in 3NF.

5) Describe if you consider formulating any views and what type would they be (materialized, updatable, etc).

You use views for two reasons: 1) access control, 2) performance. If you have a large table but only part of the table (rows and/or columns) can be accessed by some users, then you can define a view and grant access to the view to those users. If you have queries that needs complicated computation, such as those that involve multi-level aggregation, then you can define materialized views for reduce redundant computation. You do not have to define both types of views. For each view, please state the intended use of the view.

6) Investigate what capacity to administer privileges your RBDMS supports and indicate the roles of users of your database.

This is the user roles and privileges in your system.

Implementation:

You will need to implement your proposed system based on the design. If your system is large, you may implement only a part of it (to reduce programming). In any case, your implementation must include:

1. A user interface through which users can submit queries or update requests to the DBMS.

2. A data maintenance component that allows users to insert, delete, update data in the database.

3. A query component that implements the important Queries in your physical design of Task 2.

4. At least one meaningful transaction, one trigger, and one stored program (stored procedure or stored function).

5. A report in PDF that describes your transactions, triggers and stored programs in point 4, as well as any updates of your physical design.

6. A concise user manual in PDF that lists the system installation (application platforms, programming languages, environment setup and so on), functionalities, and how the system can be used. If your system is uploaded into a publically accessible web server, please indicate the URL.

Attachment:- Database Systems and Administration.rar