Need some help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need some help

Author

Topic

Marcuslogen
Starting Member

1 Post

Posted - 2013-11-02 : 10:06:44

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-02 : 10:21:40

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-02 : 13:47:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources