Ok for my coursework i have to draw a data model for any scenario of my choice, we are allowed to take an existing data model from the internet, problem is, the teacher has been VERY specific about the relationships that have to be included in the model, and im finding it extremelly difficult to find something that possesses all these elements.

Could someone help me? Suggest a scenario to use and give examples of how each of these points would be covered? Or draw me up an example?

• At least one one-many relationship
• At least one decomposed many-many relationship-show the original many-many relationship
• At least one recursive relationship
• At least one one-one relationship
• An entity with subtyping that has relationships with other entities that involve the super-type and at least one subtype.
• No less than 8 entities, no more than 10

Thank you!

Draw up a model for an invoice with affiliates where the affiliates have parent affiliates. Products to invoices will give you your many to many relationship.

• At least one one-many relationship

That's easy. Why not a registration system that allow a user to register several emails:
For a "users" table, have the fields

    userId        =>    primary key
    firstName
    lastName
    sex

and a table "emails":

    userFk        => foreign key to users.userId. Primary key
    email

You will have a 1 (the user profile) to many (each emails) relationship.

• At least one decomposed many-many relationship-show the original many-many relationship

I don't understand what is a "decomposed many to many" relationship, so I'm not sure but:
Given the "users" table previous example.
We will give the users the possibility to tag their profile.

To do so, you need a "tags" table:

    tagId        => primary key
    tagName

and a table to do the link between the tags and the profiles, "userTags":

    userFk        => foreign key to users.userId. First part of the composite primary key
    tagFk        => foreign key to tags.tagId. Second part of the composite primary key

This gives you a 1 to many relationship from the "users" to the "userTags" table.
And a many to one relationship from the "userTags" to the "tags" table, resulting in an effective many to many from the users to the tags.
I hope that this is the "decomposed" part.

• At least one recursive relationship

I'll take an bit of a db I work on for that.
A bit of theory first.
I work in a enterprise that produces programs to manage a pharmacy. Some medecines are to be given only upon a prescription, given by a doctor. It's a prescription.
A prescription can be renewable. The legislation here forces us to be able to trace every renewal to the first delivery. We do it that way:
The "prescription" table:

    prescriptionId        => primary key
    saleDate
    prescriptionNr
    prescriptionFk

When a first deliver is made, en entry is done into the prescription table. The field "presciptionFk" is null.
When a renewal of tha prescription is done, we insert another record in that table, but this time, the field presciptionFk have the value of the "prescriptionId" of the first delivery.
There is your recursivity.

• At least one one-one relationship

Ha, easy!
Take the "users" table from the beginning.
Create a "sex" table:

    sexId        => primary key
    sexValue

You create a 1 to 1 relationship by allowing only 1 sex to be tied for each profiles.

• An entity with subtyping that has relationships with other entities that involve the super-type and at least one subtype.
• No less than 8 entities, no more than 10

Hu !?
Here, I have to admit that I am completely lost.
What is an entity? What is subtyping and a super-type in a sql database ?

An entity with subtyping that has relationships with other entities that involve the super-type and at least one subtype.

An example:

  Person (supertype)
    Customer (subtype)
    Employee (subtype)

 Other Entities
   PaymentMethod
   Address
 
 Possible Relationships:
   1 Person has 1 or more Addresses (relationship to the supertype)
   1 Customer has 0 or more PaymentMethods (relationship to the subtype)

NOTE: If a relationship applied to all subtypes, then it applies to the supertype and not a subtype (generally speaking)

@tripy.. just think of an entity as a table

I think i'm doing understand this a bit better now..

I think im going to make an employee database.. here's what ive got so far..

theres an employee table, each employee entity has a supervisorID which is either null, or contains another employeeID, linking back to another employee entity, this sorts out the recursive bullet point

an employee uses one work station, a work station is used by one employee, this is a one-to-one relationship

regarding subtyping, i want to seperate the employees into part-time and full-time, but im having trouble knowing what i need to change to do this, do i have an employee table and two tables related to this called full_time_employee and part_time_employee? what values should each of the tables hold? :/

sorry for such a late reply, i've not had a computer for a while! hope you guys can still help me!

Thanks

I have drawn up a data model but i don't want to post it here, because i've noticed some of my classmates are finding this thread so i obviously don't want them to knick mine cause it could get lots of people into bother!

tripy or JeremyMiller ,would it be possible for you to PM me if you would like to have a look at it?

I will forward you a screenshot of it so you can see if it's any good? It isn't quite on the same lines as tripy suggested but i seem to have covered most of the points just not sure if it's feasable as a database. Thanks!

just send it to me, I'l look through it.

Originally Posted by tripy

just send it to me, I'l look through it.

okay! i shall do that now, thank you