By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Database Question

P: 34
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
Feb 26 '07 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,327
You forgot to mention what you needed help with.
Feb 26 '07 #2

P: 34
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
Feb 26 '07 #3

Rabbit
Expert Mod 10K+
P: 12,327
Which one's the primary key and which one's the foreign key?
Feb 26 '07 #4

P: 34
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
Feb 27 '07 #5

NeoPa
Expert Mod 15k+
P: 31,273
The relationship should be One-Video to Many-Loan surely?

BTW you seem to have made a good stab at the table structure :)
Feb 27 '07 #6

Rabbit
Expert Mod 10K+
P: 12,327
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.
Feb 27 '07 #7

P: 34
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
Feb 27 '07 #8

NeoPa
Expert Mod 15k+
P: 31,273
Nice way to display the relationships :)
I would say rather :
Expand|Select|Wrap|Line Numbers
  1. Member 1 : n Loan n : 1 Copy n : 1 Video
  2.  
  3. Member 1 : n Reservation n : 1 Copy n : 1 Video.
Can you confirm this is right for your situation?
Feb 27 '07 #9

P: 34
Hi,

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

Thanks
Feb 27 '07 #10

NeoPa
Expert Mod 15k+
P: 31,273
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.
Feb 28 '07 #11

P: 34
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
Feb 28 '07 #12

P: 34
Hi NeoPa

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

Thanks
Feb 28 '07 #13

NeoPa
Expert Mod 15k+
P: 31,273
See if this setup works for you.
Expand|Select|Wrap|Line Numbers
  1. Table Name=Member
  2. Field; Type; IndexInfo
  3. MemNum; Autonumber; PK
  4. MAddress; String
Expand|Select|Wrap|Line Numbers
  1. Table Name=Video
  2. Field; Type; IndexInfo
  3. Vcode; Autonumber; PK
  4. VTitle; String
  5. Date Made; Date/Time
  6. Director; String
  7. Genre; String
Expand|Select|Wrap|Line Numbers
  1. Table Name=Copy
  2. Field; Type; IndexInfo
  3. CopyNum; Autonumber; PK
  4. VCode; Numeric; FK
  5. Due Date; Date/Time     'Why in Copy Table???
Expand|Select|Wrap|Line Numbers
  1. Table Name=Loan
  2. Field; Type; IndexInfo
  3. LoanMemNum; Numeric; FK & Composite PK (CPK)
  4. CopyNum; Numeric; FK & CPK
  5. Ldate; Date/Time; CPK
Expand|Select|Wrap|Line Numbers
  1. Table Name=Reservation
  2. Field; Type; IndexInfo
  3. CopyNum; Numeric; FK & CPK
  4. resMemNum; Numeric; FK & CPK
  5. Resdate; Date/Time; CPK
  6. CollectDate; Date/Time
Can you please explain why [Due Date] is to be found in the [Copy] table?
Feb 28 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.