Physical Database Design

Database security refers to the guarding of an organization’s valuable information and data to prevent unauthorized reading access, altering or deleting of data. Among the most serious threats to security is deliberately trying to access data with no proper authority. Features must therefore be enforced in a database to provide protected and controlled access to the data contained in a database while preserving the integrity of the data at the same time.Oracle offer security checks based on a list of users who are valid, where each user is provided with a unique username and a password. This prevents unauthorized access. A security domain is assigned to each user.

There are also two types of Orace privileges; system and object privileges. System privileges allows uses to access and edit the whole system while object privileges allows the user to access specified database objects. Views are also provided in databases for security purposes. They prevent direct access of the contents of the database tables by the users.Data encryption is also another way to ensure database security. It involves altering the form of the database contents in a manner that is not easily recognizable. The data has to be decrypted for it to be meaningful.

SQL injection refers to the injection or insertion of SQL queries through the input to an application.This can be prevented by cleaning all the variables by learning them through a clean function. The other method would be overwriting mysql_query function so that it replaces your table names.

The physical database design implements the the logical data model in a database.

1.An Occupant is ALLOTED a room from CheckInDate to CheckOutDate. One

 FAMILY can be alloted more than one room.

2. The Occupant PAYS BILL. An Occupant must pay a BILL.

3. An Occupant may USE restaurant FACILITIES. One Occupant can use more than one         FACILITIES.

Mysql commands:

CREATE TABLE FAMILY:
(SSN                    NUMBER(9)          NOT NULL,
 OccupantId         VARCHAR2(20)     NOT NULL,
FamilyHeadSSN    VARCHAR2(20)     NOT NULL,
 Address              VARCHAR2(20)     NOT NULL,
 NoofAdults          VARCHARS2(20)    NOT NULL,
 NoofChildren       CHAR(20)             NOT NULL,);

CREATE TABLE ROOM(RoomNo int(5) NOT NULL,

RoomRate    mediumint(10)  NOT NULL,

RoomType    char(10)   NOT NULL,

RoomStatus   Varchar   NOT NULL,);

CREATE TABLE FOOD (

FoodId   mediumint(10),

FoodRate   varchar(10),

FoodType   char(10),

);

CREATE TABLE BILL(

BillNo   BIGINT(15),

BillDate   date,

BillTime   char(10),

);

Conclusion

A database is not complete without all the necessary security measures having been put up. SQL injection is one of the major security threats to a database. Physical database design involves the implementation of the logical database model.