Can anyone help me in Deriving of functional dependencies and Normalizing tables?

Question

I have created Restaurant e-r diagram and for my individual work, Can anyone help me inderiving of functional dependencies , Normalizing tables(BCNF form) and Construction of a database based on the obtained relations .

Update:

I created relational model and normalized it to the 3NF:

And This is my oracle SQL code:

create table restaurant(
    name varchar(20),
    city varchar(20) not null,
    contact_no char(15),
    constraint pk_name_restaurant primary key(name)
    );

create table contact_number(
    contact_no char(15),
    address varchar(50) not null,
    constraint pk_contact_no_contact_number primary key(contact_no)
    
);
create table cashier(
    cashier_id char(10)  ,
    cashier_name varchar(20) not null,
    constraint pk_chashierId_cashier primary key(cashier_id)

);

create table restaurant_has(
    name varchar(20),
    cashier_id char(10),
    constraint pk_restaurantHas primary key(name,cashier_id)
 
);

create table chef(
    chef_id char(10),
    chef_name varchar(20) not null,
    salary number(5,0),
    constraint pk_chefid_chef primary key(chef_id)
);

create table chef_works(
    name varchar(20) ,
    chef_id char(10),
    constraint pk_chefworks primary key(name,chef_id)
);


create table customer_order(
    order_no char(10),
    num_meals number(2,0),
    bill_no char(10) not null,
    constraint pk_orderno_order primary key(order_no)
);

create table chef_prepares(
    chef_id char(10),
    order_no char(10),
    constraint pk_chefprepares primary key(chef_id,order_no)
);

create table meal(
    meal_no char(10),
    quantity number(2,0),
    meal_price number(2,0),
    meal_title varchar(25) not null,
    order_no char(10) not null,
    constraint pk_orderno_meal primary key(meal_no)
);

create table meal_title(
    meal_title varchar(25),
    ingredients varchar(75) not null,
    constraint pk_mealtitle primary key(meal_title)
);

create table customer(
    cust_id char(10),
    cust_address varchar(50),
    cust_phone char(15) not null,
    constraint pk_custid_customer primary key(cust_id)
);

create table customer_phone(
    cust_phone char(15),
    cust_name varchar(20) not null,
    constraint pk_customerphone primary key(cust_phone) 
);

create table order_bill(
    bill_no char(10),
    price number(5,0) not null,
    bill_date date not null,
    cust_id char(10) not null,
    cashier_id char(10) not null,
    constraint pk_billno_orderbill primary key(bill_no)
);

alter table restaurant add constraint fk_contactNo_restaurant foreign key(contact_no) references contact_number(contact_no);

alter table restaurant_has add constraint fk_name_restauranthas foreign key(name) references restaurant(name);

alter table restaurant_has add constraint fk_cashierid_restauranthas foreign key(cashier_id) references cashier(cashier_id);

alter table chef_works add constraint fk_name_chefworks foreign key(name) references restaurant(name);

alter table chef_works add constraint fk_chefid_chefworks foreign key(chef_id) references chef(chef_id);

alter table chef_prepares add constraint fk_chefid_chefprepares foreign key(chef_id) references chef(chef_id);

alter table chef_prepares add constraint fk_chefid_chefprepares foreign key(chef_id) references chef(chef_id);

alter table customer_order add constraint fk_billno_customerorder foreign key(bill_no) references  order_bill(bill_no);

alter table meal add constraint fk_mealtitle_meal foreign key(meal_title) references meal_title(meal_title);

alter table meal add constraint fk_orderno_meal foreign key(order_no) references customer_order(order_no);

alter table customer add constraint fk_custphone_customer foreign key(cust_phone) references customer_phone(cust_phone);

alter table order_bill add constraint fk_custid_orderbill foreign key(cust_id) references customer(cust_id);

alter table order_bill add constraint fk_cashierid_orderbill foreign key(cashier_id) references cashier(cashier_id);

Answer 1

So this is an Entity Relationship Model diagram, which back in the day we also used to call a Chen diagram. For some odd reason colleges and tutorials almost exclusively teach data modelling using this type of diagram model. What's odd is that virtually no one in industry or practical DB development ever uses them, it is considered an entirely unnecessary and onerous intermediate step in data modelling and database design, we just go straight to tables, columns and relations modelling (which we call "ER Models/Diagrams" but are technically IDEFX1 model diagrams, but again no one calls them that).

Consequently what everyone coming out of school or a tutorial calls an ER Diagram and what everyone in industry and practical use thinks is an ER diagram are completely different things. Also, as you can see, many data folks with DB experience don't even know what a Chen diagram is or how to read one and so they are completely unaware that they are (almost) complete specs for the design of a database. As such the question is not at all "too broad" or inspecific. I myself had to learn how to read these diagram 20 years ago because I was interviewing so many college graduates at the time and this was the only diagramming/data-modelling technique that they knew.

I will walk through the process of reading and interpreting this diagram and explain how to turn that into Functional Dependencies and an (almost complete) data design. This is essentially the same task as converting a Chen diagram to text form, and I will leave it in text form without making the IDEFX1 (table-relational) diagram (I don't really have a good tool for it now that I'm retired). Datatypes are not specified here (which is normal for a Chen diagram) and although technically a formal relational data design does NOT require datatypes, practically speaking you do need them to finish your design and implementation. Also, there appears to be several mistakes or omissions in your Diagram which I will call out as I get to them.

So let's start with the basics of how to read a Chen diagram:

Legend:

• The (blue) rectangles become tables which are Codd "relations" (intra-table).
• The ovals or "bubbles" are columns in the table they are attached to,
• Ovals with underlined text represent the primary key for the table they are attached to,
• The (blue) diamonds represent Chen relations (inter-table) that will usually become your Foreign Key constraints,
• The lines from a diamond (relation) to a rectangle (table) also indicate cardinality. An arrow means a cardinality of one, while no arrow is means "many" (this actually varies a lot in different "styles" so I am guessing as to which style you are using for this).

So now we can go through the diagram, first looking at the tables and columns and derive the intra-table functional dependencies from them. Intra-table functional dependencies go from the primary key columns to all of the other columns (if there are alternate keys then these would be additional functional dependencies)

Tables Functional Dependencies:

• Cashier: Cashier_Id --> CashierName
• Restaurant: Name --> Address, ContactNo
• Chef: Chef_Id --> ChefName, Salary
• Bill: Bill_No --> Price, OrderDetail
• Customer: Cust_Id --> Cust_Name, Cust_Phone, Cust_Address
• Meal: Meal_No --> Meal_Title, Meal_Price, Description, Quantity
• Order: Order_No --> #Num_Meals(??)

I have marked the #Num_Meals column as questionable because it appears to be an aggregated field which are non-relational/denormalized and thus inappropriate in the data design (they are added much later in the application design/implementation, and usually as dynamic, not static elements). But I will leave it in for clarity.

Now we are ready to look at the Chen relations to derive the inter-table functional dependencies:

Relations:

• PaidTo: Bill --> Cashier
• Pays: Bill --> Customer
• Places: Order --> Customer
• Contains: Meal --> Order (*)
• Prepare: Order --> Chef (*)
• Works: Cashier --> Restaurant (*)
• Has: Chef --> Restaurant (*)

(*) -- A note about the last four relations (Contains, Prepare, Works and Has), none of them has a connection with an arrow on it which implies that they are supposed to represent a many-to-many relation which has a couple of problems. First, Many-To-Many is not a valid relationship in relational data design and in practice requires the artificial creation of an intermediate "junction" table to change it from A:many-to-many:B into two relations between three tables: A:many-to-one:J:one-to-many:B. The other problem is that it seems clear from the context (the meaning of the table names) that you can have many Meals for one Order, but never many Orders for one Meal and thus the diagram is clearly wrong on that one.

Therefore, to avoid getting into the complexity of Junction tables I have just assumed that the diagram is wrong for these relations and that there is supposed to be arrows from <Contains> to [Order] and from <Prepare> to [Chef], etc. If you know for certain that these are supposed to be Many-to-Many relationships then you will have to incorporate that.

Now all we need to do is to combine and reduce the two lists of functional dependencies so that there are no redundancies. You can change the related-tables FDs into table-columns FDs simply by replacing the table names with the corresponding column names of their primary keys. For instance, the PaidTo relation's Bill --> Cashier would become Bill_No --> Cashier_Id. Once you have changed all of the FDs into table-column FDs, then you want to remove any redundancies by combining any FDs that have the same "key" on the left-hand side. Thus, Bill_No --> Cashier_Id and Bill_No --> Price, OrderDetail would be combined into Bill_No --> Price, OrderDetail, Cashier_Id.

Final Functional Dependencies:

• Cashier: Cashier_Id --> CashierName, Restaurant(Name)
• Restaurant: Name --> Address, ContactNo
• Chef: Chef_Id --> ChefName, Salary, Restaurant(Name)
• Bill: Bill_No --> Price, OrderDetail, Cashier_Id, Cust_Id
• Customer: Cust_Id --> Cust_Name, Cust_Phone, Cust_Address
• Meal: Meal_No --> Meal_Title, Meal_Price, Description, Quantity, Order_No
• Order: Order_No --> #Num_Meals(??), Cust_Id, Chef_Id

Warning, different people, teachers, instructors and tutorials may do this (reduction) differently and thus end up with a different final list. You will have to review the rules that your course instructor expects and apply them here.

Finally, we are are ready to do the Table and Relations implementation/design. There is not enough information/context for me to do this all the way to BCNF for certain, but I can get it pretty close based on what we have above:

TABLE Restaurant
(   Name Primary Key,
    Address,
    ContactNo
)

TABLE Cashier
(   Cashier_Id Primary Key,
    CashierName ,
    Restaurant_Name FOREIGN KEY References Restaurant(Name)
)

TABLE Chef
(   Chef_Id Primary Key,
    ChefName,
    Salary,
    Restaurant_Name FOREIGN KEY References Restaurant(Name)
)

TABLE Bill
(   Bill_No Primary Key,
    Price,
    OrderDetail,
    Cashier_Id FOREIGN KEY References Cashier(Cashier_Id)
    Cust_Id FOREIGN KEY References Customer(Cust_Id)
)

TABLE Meal
(
    Meal_No Primary Key,
    Meal_Title,
    Meal_Price,
    Description,
    Quantity,
    Order_No FOREIGN KEY References Order(Order_No)
)

TABLE Order
(
    Order_No Primary Key,
    #Num_Meals (??),
    Cust_Id FOREIGN KEY References Customer(Cust_Id),
    Chef_Id FOREIGN KEY References Chef(Chef_Id)
)

Note that there are no datatypes below because none were provided in the diagram. None of this will actually compile without specifying datatypes for each column. Also, the word "Order" is a reserved keyword in almost all databases, so you may want to change the name of the Orders table to something like 'Order_" to avoid any problems (otherwise you will have to explicitly quote this name in all of the SQL that refers to it).

One last thing I will note: It seems like the Bill table should have a relation/reference to the Order table but it does not (the OrderDetail column does not do this according to your diagram).