As part of my assessment I have to build a non-trivial database system for web enabled application for the business domain proposed here. This will be a sample database application completely done using SQL PLUS (Oracle 10g)
The database will include different modules for manage, they are:
1) Client Management – This module will offer Adding new clients; edit client information; manage clients’ invoices.
2) Staff Management –This section will allow Adding new staff; edit staff information; manage staff invoices.
3) Project Management – This part will help Adding new project; edit project information/status; assign to staff; change assignments; mark parts and stages as complete/incomplete; mark project as complete.
4) Staff login – This module will enable the staff to View current projects, past projects; download input files, upload output files; send and receive messages to management; create invoices, create time sheet.
5) Client login – This module will enable the clients to View current projects, past projects; upload input files for new or existing projects, along with message; download output files; view/print/save current and past invoices; pay invoices through specified gateway.
6) Documents management – View all documents stored on site, according to categories in which they are stored; edit features of storage.
The database instruction:
You need to decide about the required tables, views, indexes, etc. . Furthermore, you need to outline the appropriate distribution and storage requirements for tablespaces and datafiles and calculate the storage requirements for these and other database objects. The system must manage different levels of access/security/permissions and therefore you may also need to create roles and grant these to the different users of your system.
You need to create objects and provide scripts for creation of following:
a) Tables, indexes, views, database triggers and functions or built in procedures.
b) The different users of your system.
c) The different database roles and the granting of these privileges.
You need to provide scripts for creation of the following [Do not create these objects]:
d) Appropriately sized and distributed Tablespaces.
e) Appropriately sized and distributed Datafiles.
You should create your schema in DBS database as follows :
• Create a master schema PROJG03. All tables, views etc that you create should belong to this user.
• Create others users and assign them roles as per your application requirement.
• Use the following assignments for all users (including PROJG03) : default tablespace DBS_SPACE, temporary tablespace TEMP_SPACE and give 5M of QUOTA in DBS_SPACE. When creating tables, to save space, use INITIAL and NEXT size of 8K regardless what sizes are calculated, however calculated values should be put in documentation.
Your application will typically use the pre-configured Uniform Server (see instructions in attached document – “[url removed, login to view]”) and use PHP to connect to the DBS Database. Several examples of how your PHP code can connect to the DBS database are provided. Other resources, including PHP and Oracle manuals, are available for reference on the course website. Configuration support will be limited to the PHP/Uniform Server environment. Your application must demonstrate:
• Ability to query tables and views and display the results appropriately.
• Ability to add, delete, and modify records.
You need to submit:
2. An ER diagram
3. Scripts for creating any tablespaces that you think will be applicable to your application (Note: do not create these tablespaces – use the default tablespace for all of your objects).
4. Scripts for creating users, roles and granting of privileges as required.
5. Scripts for creation of all Tables, Indexes and other database objects (views, sequences, database triggers, stored procedures or functions, etc.)
o All objects should be appropriately sized (INITIAL, NEXT, PCT_FREE, etc).
o Indexes should be created on appropriate columns.
o Tables should translate directly from your ER diagram.
6. Object/sizing rationale:
o Explain your table/index sizing. Eg: Initial number of rows and expected growth.
o Explain why you created your set of indexes and why they will improve performance of your application.
o List any indexes that were automatically created and why.
7. The application itself (do not attach PHP code to documentation – some screenshots are permitted).