validation ERD

Question

validation ERD

rick.adams

SSC-Addicted

Points: 485
More actions
January 20, 2017 at 6:14 am

#334132

I looking for feedback about this ERD to make it a valid ERD to the constraints.

1. Only the Bikes (fiets) have inshurence.
2. bikes are able to swap if there is a spare, not recerverd bike
3. email is unique
How do i make the ERD valid?
Do i need to change the FK between (verzekering) inshurense table and (verhuur) rent table?
Or do i need to add a FK to the verhuur table so the it's unique to add the FK relationship fiets_id in the entity verzekering?
For number 2-3, is it smart to create a procedure instead off a trigger?
Looking for your replay,
Best,
Rick

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

rick.adams - Friday, January 20, 2017 6:14 AM
I looking for feedback about this ERD to make it a valid ERD to the constraints.

1. Only the Bikes (fiets) have inshurence.
2. bikes are able to swap if there is a spare, not recerverd bike
3. email is unique
How do i make the ERD valid?
Do i need to change the FK between (verzekering) inshurense table and (verhuur) rent table?
Or do i need to add a FK to the verhuur table so the it's unique to add the FK relationship fiets_id in the entity verzekering?
For number 2-3, is it smart to create a procedure instead off a trigger?
Looking for your replay,
Best,
Rick

Hi Rick,
could you attach the ERD as the image is too small to be readable?
😎

rick.adams SSC-Addicted Points: 485 More actions · Answer 2

Eirikur Eiriksson - Friday, January 20, 2017 6:36 AM
rick.adams - Friday, January 20, 2017 6:14 AM
I looking for feedback about this ERD to make it a valid ERD to the constraints.

1. Only the Bikes (fiets) have inshurence.
2. bikes are able to swap if there is a spare, not recerverd bike
3. email is unique
How do i make the ERD valid?
Do i need to change the FK between (verzekering) inshurense table and (verhuur) rent table?
Or do i need to add a FK to the verhuur table so the it's unique to add the FK relationship fiets_id in the entity verzekering?
For number 2-3, is it smart to create a procedure instead off a trigger?
Looking for your replay,
Best,
Rick
Hi Rick,
could you attach the ERD as the image is too small to be readable?
😎

yes, see attachment.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

I see many problems here and my first thought is that a re-iteration of the business requirement analysis should be the next step. The schema itself is over simplified for the role and has some other serious flaws to, such as clear text passwords etc.

😎

rick.adams SSC-Addicted Points: 485 More actions · Answer 4

Eirikur Eiriksson - Saturday, January 21, 2017 12:43 AM
I see many problems here and my first thought is that a re-iteration of the business requirement analysis should be the next step. The schema itself is over simplified for the role and has some other serious flaws to, such as clear text passwords etc.
😎

Hi Eirikur,

An explanation of the problems and serours flaws is helpful for the rework is helpfull.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

rick.adams - Saturday, January 21, 2017 5:06 AM
Eirikur Eiriksson - Saturday, January 21, 2017 12:43 AM
I see many problems here and my first thought is that a re-iteration of the business requirement analysis should be the next step. The schema itself is over simplified for the role and has some other serious flaws to, such as clear text passwords etc.
😎
Hi Eirikur,
An explanation of the problems and serours flaws is helpful for the rework is helpfull.

Can you please post the full business requirements that the schema must support? Then we can start to iterate through different applicable sub-patterns and sections of the requirements, otherwise one would only be guessing and throwing in assumptions.
😎

rick.adams SSC-Addicted Points: 485 More actions · Answer 6

With pleasure, here are the Business Requirements:
1. Only the bicycle (fiets) have inshurence.
2. Bicycle (fiets)are able to swap if there is a spare, not recerverd bike
3. Email must be unique
4. The staff worsk in multiple jobroles.
5. Accessories alike hemlets ectr. Are avible to rent only if the customer rents a bicycle (see fiets table)
6. The sex off the customer needs to be; ‘M’ (male) or ‘F’ (Female)
7. The final rentdate from rent (bikes and/or accessories) needs to be a on a later date then the rent startdate
8. The damage paydate must be later the damage startdate

9. Seven relevant constraints (based on stored procedures )
9.1. insert rules:
- email must be unique,
- rent date must be on or after the current date
9.2. Update constrains:
- cannot make a booking if email is NULL
- cannot change ID
- returns a error if the ID does not exist
9.3. Delete Rules:
- only able to delete one record at a time
- maintain a log when a account is created, updated, deleted for datamining.

10. Sort all bicycles on type [electric, male/female)
11. Create views via userinput the following results for one month, per quarter, or for one year:
        - a damagereport for the bicycles only (becaue the accessories are NOT insured.)
        - all the rents based on a selected klant_ID.
        - all customers who have rended a bicycle
        - all the damaged bicycle within the selected periode.
        - all customers who have rented a bicycle within a timeframe listed above.
        - the revenue for a selected month, quartal and year.
        - the most rented bicycle
         - the lease with the highest turnover.
         - the loss per year
         - bike with the most damage
         - customers with unpaid bills
12.    Show a distinct view of bicycles and accessories based on a specific (rent_ID) ) verhuur_ID

rick.adams SSC-Addicted Points: 485 More actions · Answer 7

rick.adams - Saturday, January 21, 2017 8:26 AM

With pleasure, here are the Business Requirements:
1. Only the bicycle (fiets) have inshurence.
2. Bicycle (fiets)are able to swap if there is a spare, not recerverd bike
3. Email must be unique
4. The staff worsk in multiple jobroles.
5. Accessories alike hemlets ectr. Are avible to rent only if the customer rents a bicycle (see fiets table)
6. The sex off the customer needs to be; â€˜Mâ€™ (male) or â€˜Fâ€™ (Female)
7. The final rentdate from rent (bikes and/or accessories) needs to be a on a later date then the rent startdate
8. The damage paydate must be later the damage startdate

9. Seven relevant constraints (based on stored procedures )
9.1. insert rules:
- email must be unique,
- rent date must be on or after the current date
9.2. Update constrains:
- cannot make a booking if email is NULL
- cannot change ID
- returns a error if the ID does not exist
9.3. Delete Rules:
- only able to delete one record at a time
- maintain a log when a account is created, updated, deleted for datamining.

10. Sort all bicycles on type [electric, male/female)
11. Create views via userinput the following results for one month, per quarter, or for one year:
        - a damagereport for the bicycles only (becaue the accessories are NOT insured.)
        - all the rents based on a selected klant_ID.
        - all customers who have rended a bicycle
        - all the damaged bicycle within the selected periode.
        - all customers who have rented a bicycle within a timeframe listed above.
        - the revenue for a selected month, quartal and year.
        - the most rented bicycle
         - the lease with the highest turnover.
         - the loss per year
         - bike with the most damage
         - customers with unpaid bills
12.    Show a distinct view of bicycles and accessories based on a specific (rent_ID) ) verhuur_ID

[/quotW