I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned. I have tried to the following:
--create the database create database RentACar; use RentACar; --create the tables create table Clients( Id int not null constraint pk_ClientID primary key, Name nvarchar(255), LastName nvarchar(255) ); create table CarPool( Id int not null constraint pk_CarPoolID primary key, Brand varchar(40) not null, Price decimal not null ); create table DateOut( Id int not null constraint pk_DateOutID primary key, DateOut datetime not null ); create table DateIn( Id int not null constraint pk_DateInID primary key, DateIn datetime not null ); --fill the tables with some values insert into Clients values (1, 'Richard', 'Castle'); insert into Clients values (2, 'Jane', 'Rizzolli'); insert into Clients values (3, 'Jack', 'Bauer'); insert into CarPool values (1, 'BMW', 125.99); insert into CarPool values (2, 'Lexus', 105.99); insert into CarPool values (3, 'Honda', 95.99); insert into DateOut values (1, 2012-03-10); insert into DateOut values (2, 2012-03-11); insert into DateOut values (3, 2012-03-12); insert into DateIn values (1, 2012-04-10); insert into DateIn values (2, 2012-04-11); insert into DateIn values (3, 2012-04-12);
--now try to connect the tables somehow...
I don't know how to connect the tables with primary and foreign keys, so as to be able to for example query a list of all people who did not return the car yet, and a field that calculates how long a car has been with a client. I would also like to create a query that allows me to update the datetime for clients that have not returned their cars.
I am only familiar with the basic SQL commands like create, add, update, insert into, and i am at the end of my wits here. Nothing I try works. I always get errors no matter what i try. I am going slowly through MS Tranact-SQL documentation but I cannot seem to find what I am looking for. I know the tables should be normalized and kept to a minimum, data should not be redundant etc... I just can't seem to find the right combination. Can someone please point me in the right direction?
@BryceAtNetwork23 thanks for editing my post with code.
@Lumpy thank you for your help. I didn't have time to check if someone had replied to my question here, and last night i was thinking how to finally solve this and came up with the same conclusion. I should have two tables, one for Clients and one for the Cars, and then one table to connect them, by simply creating a column with both dates, in and out, and then the key fact being the two columns that reference the IDs of the particular Client and a particular Car using foreign keys. Also the date(s) being in the same row with these Client ID and Car ID is the connection to the Client and Car tables. Eureka! I felt so stupid, haha, it was right there in front of my nose.
Anyway, I think SQL is amazing, and I am just beginning to grasp the possibilities as I go deeper into things, and thank God for dba.stackexchange and folks like yourself, it makes learning a whole lot easier.