Basic RDBMS design and schema creation in Oracle

Question

Hi,

I have to create a entire application in an Oracle RDBMS with SQL. The Design includes to complete full entity-relationship and also design the relevant Schema with object creations.Important is-When creating schema and objects..we should fully apply normalisation. Also, writing all the relevant SQLs in each and every step in this process.

I need your help on the fully design and implementation of the same. I am to complete this as a part of School Project.

The scenario:-

There is a COMPANY(STAR INTERNATIONAL*) WHICH has many divisions/departments in it. These divisions has people who are employees working for them. The COMPANY has numerous Products(also we can say projects) and these project work is done by the various depts. Also,here things such as (1)Employees can work or be a part of many or multiple departments and work for multiple projects/products at a time (2)As usual-each Department has a manager.**EVery dept can handle or work only on one project.

Q. How would you start with the DBMS design for the above item? i am very new to the entire subject and reading definitions, but i feel after i am able to complete this with all your help-i will understand.

How many tables would be needed for each of the items and what all attributes would each have.Could you all please help and give the tables and columns and also relationship between them. how would you apply normalization while designing.examples with sqls for the above item would be helpful.

Answer 1

Take a look at DatabaseAnswers for a schema starting point.

Answer 2

Some hints to get you started, with examples from a different context, so there is some thinking left for you to do:

Identify the Objects/Entities these will become you main tables. e.g. when modeling a CD collection a CD might be an entity, a Track might be another one

Identify the attributes of the entities e.g. a CD has a title and a release date

Identify relations between the Entities e.g. a Track belongs to a CD or if you prefer a CD has Tracks there is also a relation between CDs and their interprets

Identify the cardinality of the relations e.g. a CD has many tracks, but a track belongs only to one CD; a CD may have many interprets, and a interpret may have many CDs

If you proceed through these steps and put the results in your post, I and/or others will give feed back and more hints, how to proceed