SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need some help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marcuslogen
Starting Member

Netherlands
1 Posts

Posted - 11/02/2013 :  10:06:44  Show Profile  Reply with Quote
Hello everyone,

I have been strengeling with some databases for a while but i just dont get the hang of it..

My question if someone can help me with this problem.
I got a script but i dont know were to put the primary keys and foreign keys.... can someone help?

--TABLE: Auto TYPE:
------------------------------------------------
CREATE TABLE AUTO
(
KentekenID VARCHAR(10),
OnderhoudID NUMBER,
SchadeID NUMBER,
PrijscodeID NUMBER,
Merk VARCHAR(20),
Type VARCHAR(20),
Brandstof VARCHAR(20),
Bouwjaar DATE,
Accesoires VARCHAR(200),
Mankementen VARCHAR(200),
Datum_Aankoop DATE,
Datum_Verkoop DATE,
Bedrag_Aankoop NUMBER,
Bedrag_Verkoop NUMBER,
Totaal_Kosten_Onderhoud NUMBER,
Gegevens_Koper VARCHAR(200),
Gegevens_Verkoper VARCHAR(200)

);
------------------------------------------------


--TABLE: Onderhoud TYPE:
------------------------------------------------
CREATE TABLE ONDERHOUD
(
OnderhoudID NUMBER,
KentekenID VARCHAR(10),
Omschrijving VARCHAR(200),
Kosten NUMBER,
Onderhoud_Datum DATE
);
------------------------------------------------


--TABLE: PRIJSCODE TYPE:
------------------------------------------------
CREATE TABLE PRIJSCODE
(
PrijscodeID NUMBER,
Prijs NUMBER,
Omschrijving VARCHAR(200)
);
------------------------------------------------


--TABLE: SCHADE TYPE:
------------------------------------------------
CREATE TABLE SCHADE

(SchadeID NUMBER,
Omschrijving VARCHAR(200),
Kilometerstand NUMBER,
Plaats VARCHAR(30),
Datum DATE,
Toedracht VARCHAR(200),
Tegenpartij_Gegevens VARCHAR(200),
Aansprakelijkheid VARCHAR(200));

------------------------------------------------


--TABLE: VERHUUR TYPE:
------------------------------------------------
CREATE TABLE VERHUUR
(
KentekenID VARCHAR(10),
KlantID NUMBER,
Huur_Start DATE,
Huur_Eind DATE,
Volgetankt VARCHAR(10),
Huurprijs NUMBER,
Korting NUMBER
);
------------------------------------------------


--TABLE: KLANT TYPE:
------------------------------------------------
CREATE TABLE KLANT
(
KentekenID VARCHAR(10),
KlantID NUMBER,
FactuurID NUMBER,
RetourID NUMBER,
Voornaam VARCHAR(30),
Achternaam VARCHAR(30),
Straatnaam VARCHAR(30),
Huisnummer NUMBER(7),
Woonplaats VARCHAR(30),
Postcode VARCHAR(6),
Rijbewijsnummer VARCHAR(10),
Telefoonnummer VARCHAR(10),
Bankrekeningnummer VARCHAR(10),
Geboortedatum DATE,
Geslacht VARCHAR(10),
Zwartelijst VARCHAR(10)
);
------------------------------------------------


--TABLE: RETOUR TYPE:
------------------------------------------------
CREATE TABLE RETOUR
(
RetourID NUMBER,
Schade VARCHAR(200),
Maknementen VARCHAR(200),
Volgetankt VARCHAR(10),
Kilometerstand NUMBER
);
------------------------------------------------


--TABLE: FACTUUR TYPE:
------------------------------------------------
CREATE TABLE FACTUUR
(
FactuurID NUMBER,
RetourID NUMBER,
GeredenKM NUMBER,
Factuurbedrag NUMBER,
Schade_eigen_risico VARCHAR(10),
Factuur_Datum DATE,
Verhuurdagen NUMBER,
Korting NUMBER,
Aantal_Aanmaningen NUMBER,
Eerste_Aanmaandatum DATE,
Tweede_Aanmaandatum DATE,
Datum DATE,
Vervaldatum DATE,
Incassobureau VARCHAR(10),
Zwartelijst VARCHAR(10),
Betalingsmethode VARCHAR(15)
);

James K
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 11/02/2013 :  10:21:40  Show Profile  Reply with Quote
Which columns you use for primary keys, and what foreign key relationships you define is entirely dependent on your business requirements and the nature of the data.

In any table, there may be what they call a candidate key - which is a column or group of columns together form a unique value. So for example, if you had a CUSTOMER table, customer_id perhaps could be a primary key - there would be only one customer with a given customer_id. On the other hand, you would not want a customer_name to be a primary key, because there may be more than one customer with the same name.

So analyze your business needs and figure out what the primary key candidates are. Read up a bit on primary keys - for example here: http://www.w3schools.com/sql/sql_primarykey.asp

That page also has some nice descriptions and guidance for foreign keys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/02/2013 :  13:47:33  Show Profile  Reply with Quote
I dont think you're using SQL Server though as I can see NUMBER etc as datatypes which doesnt exist in SQL Server.
Anyways determining which columns to be chosen as Primary Key would be similar across different DBMS systems
Whereas actual creation script syntax may vary from one product to other

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000