Database Question

[thinboy]

Hi

I am doing a scenario for my compnay which is a video library, i have the following entities but need help with the database using MS Access 2003 as iam new to this piece of software:

The entities i have got are as follows

Video (Vcode, VTitle, Date Made, Director, Genre)
Copy (CopyNum, Vcode, Due Date)
Loan (LoanMemNum, CopyNum, Ldate)
Member (MemNum, MName, MAddress)
Reservation (VCode, resMemNum, Resdate, CollectDate

[Rabbit]

You forgot to mention what you needed help with.

[thinboy]

Hi,

I need help with my database, when i go to make the relationship in access using my entites in the previous post, i cannot get the relationship from loan to video to be a one to many, because access put it as many to one

Loan (LoanMemNum, CopyNum, Ldate)
Video (Vcode, VTitle, Date Made, Director, Genre)

Is this possible? Do i need to add anymore entities?

Any help on this matter would be appriciated

[Rabbit]

Which one's the primary key and which one's the foreign key?

[thinboy]

Hi the primary key are underlined:

Video (Vcode, VTitle, Date Made, Director, Genre)
Loan (LoanMemNum, CopyNum, Ldate)

Thanks, Also would there be an easier solution to this as i have encountered some problems when put in a relationship in access

[NeoPa]

The relationship should be One-Video to Many-Loan surely?

BTW you seem to have made a good stab at the table structure :)

[Rabbit]

So VCode = LoanMemNum? If not then you need VCode in your Loan table.

And NeoPa's right, it should be one-to-many from Video to Loan.

[thinboy]

Hi so the relationship will be as follows

Member 1 : n Loan n : 1 Video 1 : n Copy

Member 1 : n Reservation n : 1 Video.

Is this correct as i was told to do it a different way as stated in my previous post, do i need to add anymore entites to my tables?

Thanks

[NeoPa]

Nice way to display the relationships :)
I would say rather :

Member 1 : n Loan n : 1 Copy n : 1 Video
 
Member 1 : n Reservation n : 1 Copy n : 1 Video.

Can you confirm this is right for your situation?

[thinboy]

Hi,

I need a little help with functional and non functional requirements, can someone please help me

Thanks

[NeoPa]

I can't and won't answer questions with such a broad scope.
Your best approach is to ask discrete questions on a particular issue. Smaller bites are much easier to handle and less daunting for the potential helper. A good idea is to respond to all posts in your thread too. You've left my question hanging, what response do you expect in those circumstances? It is after all, your own thread.

[thinboy]

Hi,

Thanks eveyone for all your help, i am current stuck i need to have a n: 1 relationship between Reservation and Video, the entities are as follows

Reservation (VCode, resMemNum, Resdate, CollectDate)
Video (Vcode, VTitle, Date Made, Director, Genre)

Here is what it says in access:

Data in the Reservation table violates referential integrity

Thanks

[thinboy]

Hi NeoPa

Ive used your example buT i cannot get the relationship between RESERVATION and COPY to be a n:1

Thanks

[NeoPa]

See if this setup works for you.

Table Name=Member
 Field; Type; IndexInfo
MemNum; Autonumber; PK
MAddress; String

Table Name=Video
 Field; Type; IndexInfo
Vcode; Autonumber; PK
VTitle; String
Date Made; Date/Time
Director; String
Genre; String

Table Name=Copy
 Field; Type; IndexInfo
CopyNum; Autonumber; PK
VCode; Numeric; FK
Due Date; Date/Time     'Why in Copy Table???

Table Name=Loan
 Field; Type; IndexInfo
LoanMemNum; Numeric; FK & Composite PK (CPK)
CopyNum; Numeric; FK & CPK
Ldate; Date/Time; CPK

Table Name=Reservation
 Field; Type; IndexInfo
CopyNum; Numeric; FK & CPK
resMemNum; Numeric; FK & CPK
Resdate; Date/Time; CPK
CollectDate; Date/Time

Can you please explain why [Due Date] is to be found in the [Copy] table?