HOMEWORK TEXT
The "Virtual foundation for trading and distribution" is considered one of the biggest hypermarkets of the town. It offers a lot of different types of merchandise (electrical equipments, narration goods, kids toys … etc).
This foundation has several sales points, and it uses bills to organize the selling. The client enters a branch and chooses the material he wants to buy and then he pays the money at a sales point that prepares a bill to him.
To encourage the clients, the sales department decides to grant points to client when he buys some goods, and the client can replace these points by a gift.
The foundation has a central store which is devised into specialized sub stores. The purchasing operations are done through the store department. The store manager needs some information to do his job like:
- Alerts if some materials reached the allowed minimum limits.
- Alerts if the expiry date of some materials passed.
- The quantities that exist in the sub stores.
- The consumed materials.
The Management department has interests in some statistics like:
- The most sold item.
- The most active sales point.
- Having periodical list of sales and material movements. (Daily, weekly, monthly …etc).
And it has interest in determining the employee that made each material movement (sales, canceling, purchasing … etc).
The head quarter of the foundation decided to build an information system.
Suppose you have the logical design of the database as ERD, and you are the database administrator, and your implementation in Oracle9i R2 of this ERD should accomplish:
- Best performance of the database through the shortest response time in the sales points.
- Suitable privileges to each user.
- Suitable storage parameters for each object.
- Distributing database files through many hard drives to increase the safety level and to increase the performance.
Your server has at least 6 Hard disks with 200 GB each, a RAM of 1 GB, and with Windows 2003 server. Your hard drives are C, D, E, F, G, and H.
REQUESTS:
You are asked to build the database according to the attached ERD, through the followings:
Part I Building your oracle database:
1- Initialization Parameters and other Characteristics:
a. Determine the character set of your database.
b. Determine your database block size, justify your answer.
c. Determine the sizes of each part of the SGA, justify your answer.
d. Determine the location of your trace and diagnosis files.
e. Determine the DB configuration that should be used for connection, justify your choice.
f. Write your initialization parameters file, choose its location.
2- Logical Structure:
a. Determine the tablespaces needed to build the DB, justify your answer.
b. Determine the storage parameters for each tablespace, justify your answer.
c. Determine the management method for each tablespace, justify your answer.
d. Determine the data block size for each tablespace, justify your answer.
3- Physical Structure
a. Determine the control files count and locations.
b. Determine the redo log groups and members count and the distributions of their members.
c. Determine the distribution of datafiles on tablespaces and hard drives.
4- Users and privileges
a. Determine how many Oracle users we need and their roles (Including the DBAs)
b. Distribute the DB objects ownership over the suggested users.
c. Distribute the privileges on the suggested users in a manner that every user will access only what he needs.
5- Building your DB
Build your database using DBCA or manually. Deliver all your scripts including "Create Database" command.
Part II Database Tables and Indexes
Write and execute the create command of all your tables and necessary indexes including:
1- Tables and indexes tablespaces.
2- Storage parameters for the best performance, justify your answer.
Part III Database Availability and Safety
1- Explain how you assure the availability of your database in case of:
a. Loosing a redo log group.
b. Loosing a control file.
c. Media failure (case of loosing one HD).
2- Explain your vision to guarantee the availability and safety of the data in this foundation (your recommendation of the hardware and software).
