Vuk Dencic Mandatory 2 inf115 23.03.
2022
Før dere begyner å rette denne så jeg skal bare si at de ER diagramene jeg prøvde ulike tools. Jeg leste
ikke den øverste teksten i oppgave arket. Den siste ER diagram måtte jeg skrive på papir fordi jeg er i
utlandet nå, så jeg bare tok det sånn rask på papir håper det er ikke noe problem :D.
Question 1
Vuk Dencic Mandatory 2 inf115 23.03.2022
Question 2
1)
There is not connection between bike and user so if we put subscription into user so we can get a
unwanted relation.
2)
Create Table USER
USERID int identity(1,1) NOT NULL,
Name nvarchar(200) NOT NULL,
PhoneNumber number(10) NOT NULL,
Latitute float(10,6) NOT NULL,
Longtitute float(10,6) NOT NULL,
PRIMARY KEY (USERID)
Create Table STATION
StationID int identity(1,1) NOT NULL,
Name nvarchar(200) NOT NULL,
Latitute float(10,6) NOT NULL,
Longtitute float(10,6) NOT NULL,
MaxParkSpots nvarchar(200) NOT NULL,
AvailableParkSpots nvarchar(200) NOT NULL,
PRIMARY KEY (StationID)
)
Vuk Dencic Mandatory 2 inf115 23.03.2022
Create Table Bike
BikeID int identity(1,1) NOT NULL,
Name nvarchar(200) NOT NULL,
CurrentStatus nvarchar(200) NOT NULL,
ReparationStatus nvarchar(200) NOT NULL,
StationID int,
PRIMARY KEY (BikeID),
FOREIGN KEY (StationID) REFERENCES STATION(StationID)
Create Table SUBSCRIPTION
SubscriptionID int identity(1,1) NOT NULL,
Status nvarchar(200) NOT NULL,
Start datetime,
End datetime,
Type nvarchar(100),
UserID int,
BikeID int,
PRIMARY KEY(SubscriptionID),
FOREIGN KEY(UserID) REFERENCES USER(UserID),
FOREIGN KEY(BikeID) REFERENCES BIKE(BikeID)
)
Vuk Dencic Mandatory 2 inf115 23.03.2022
3)
Vuk Dencic Mandatory 2 inf115 23.03.2022
Question 3
1)
Entity: Trip
Attributes: user, bike, starttime time, end time, start station, end station
CREATE TABLE TRIP(USER VARCHAR(200), BIKE VARCHAR(200), START TIME DATETIME(), END TIME
DATETIME(), START STATION VARCHAR(200), END STATION VARCHAR(200))
2)
Vuk Dencic Mandatory 2 inf115 23.03.2022
3)Normalization
Every data which we are storing in the databse must maintain integrity, should not have
redundant data . so with the help of normalization we can mainatin a clean data in the
database
There are 4 different types of Normalization
1) First Normal form(1NF)
2)Second Normal Form (2NF)
3)Third Normal Form(3NF)
4)Boyce-codd Normal form(BCNF)
FIRST NORMAL FORM(1NF):
Vuk Dencic Mandatory 2 inf115 23.03.2022
A table is said to be in 1NF if the atomocity of table is 1
ATOMICITY: IT defines a coloumn in the table should not hold more than one value
SECOND NORMAL FORM(2NF)
A table is said to be in 2NF if
1) it should be in 1NF
2) It should not have partial dependancy
PARTIAL DEPENDANCY:subset of canditate key should give a non prime attributes
CANDITATE KEY: which uniquely identifies the table which is also referred as primary key
NON PRIME ATTRIBUTES: Attributes which are not part of canditate key are said to be non prime
attributes
THIRD NORMAL FORM(3NF)
A table is said to be in 3NF if
1)it should be in 2NF
2) there should be no transitive dependancy
TRANSITIVE DEPENDANCY: non prime attribute should not depend on other non prime attribute
BOYCE-CODD NORMAL FORM(BCNF)
A table is said to be in BCNF if
1) it should be in 3NF
2) Right hand side of functional dependancies should depend on super key
SUPER KEY:set of one or more coloumns that uniquely idemtify the table .primary key is a
subset of super key.
Question 5
Subproblem 1
1NF
A table is said to be in 1NF if it does not contain any composite or multivalued attributes and does not
contain any repeating groups.
2NF
Vuk Dencic Mandatory 2 inf115 23.03.2022
A table is said to be in 2NF, if it is in 1NF and every non key attribute is fully functionally dependent on
the whole of the primary key without any partial dependencies.
3NF
A table is said to be in 3NF, if it is in 2NF and does not contain any transitive dependencies. Transitive
dependency means that any non key attribute in a table is fully functionally dependent on a candidate
key od the table which is not the primary key to be in BCNF.
BCNF
A table is said to be in BCNF if it is is 1NF, 2NF and 3NF without any overlapping candidate keys. Every
determinant in FD should be a candidate key to be in BCNF.
Vuk Dencic Mandatory 2 inf115 23.03.2022
