This is the conceptual design
Following explains the reasons for drawing the design.
Entity types
StudioBranch
· The reasons StudioBranch becomes an entity is that it says in description that the company is based on 12 recording studios all over UK and has more than one attribute.
MemberOfStaff
· This entity type has more than one occurrence and has more than one attribute. If there weren’t any staff members there can’t be a company. So MemberOfStaff is recognized as an entity type. And also there are 3 entity types which are generalized and specialized with the entity type MemberOfStaff. They are Manager, SoundEngineer and Driver.
Customer
· There is always going to be more than one customer and customer has more than one attribute. The Customer entity has a direct association with the SonicLivecompany.
TransportRecord
· TransportRecord is considered as an entity type because the records of the equipment transporting should be recorded. And also there will many number of records with more than one attribute. So TransportRecord is recognized as an entity type.
Equipment
· To hold the information about the equipments the Equipment Entity is created. It has two specialized and generalized Entities. Which are FixEquipment and PortableEquipment. Equipment entity has more than one occurrence and it has more than one attribute which are generalized. And this entity has a direct interest in the SoniLivecompany. So Equipment is recognized as an entity type.
PortableEquipmentHiringAgreement
· This entity has more than one occurrence as when a customer hires a portable equipment an agreement will be signed each time. And this entity has more than one attribute also.
FixEquipmentHiringAgreement
· This entity has more than one occurrence as when a customer hires a fix equipment an agreement will be signed each time. And this entity has more than one attribute also.
Relationship types
AllocatedTo
Manages
Registers
· Every equipment should be registered under a one branch. This relationship is important to make sure that every equipment is under responsibility of one studio branch. So that no equipment will be lost.
Keeps
It is important that the drivers keep the records of the equipment that they transport. So the drivers can always show the details of the transportation using this relationship.
Services
The description also says that All pieces of equipment must be maintained regulary. And Sound Engineer must service them and fix them if there is a fault.
Oversees
The description says that engineers should ensure that customers are provided with right type of equipment. And also it says Each engineer oversees number of customers so that when the customer get some problem the sound engineer can help him.
ExamsTheRquestFor
· The description says that when a request is submitted the sound engineer exams it check the availability of the equipment and decide whether to hire it or not.
RecordedIn
· The description says that that there must be a record with the equipment which is been transported.
RequestsFrom
· The description says that the customer need to submit a request with the details of the hiring and the submitted request will be examined by the sound engineer. So this relationship type is important.
Signs
· The reason to have such a relationship is that the description says that portable or fix equipment agreement is drawn to record the hiring information.
EnablesTheCustomerToHire
· The EnablesTheCustomerToHire relationship is important to make the agreement that is signed enable the customer to hire the equipment.
·
The MemberOfStaff
· The MemberOfStaff have generalized attributes of Driver, SoundEngineer and StudioManager entities. Those attributes are idNo,name,salary,telNo and dob. The specialized attributed of Driver entity are distancePayment and petrolCost. SoundEngineer entity has repairingFee and medicalAllowance.StudioManager has bonus and medicalAllowance The primary key in MemberOfStaff is idNo. Because every staff member has a unique idNo.
StudioBranch
· This entity also has two attributes. studioNo and city. The city of some studios will be the same. As the description says London has 4 branch studios. So branchNo attribute is important to recognize the branches. The primary key will be branchNo.
TransportationRecord
· The recordNo attribute is the primary key because it’s unique. And another important attribute is availabilityOfTheVehicle.
Equipment
· Equipment entity also has generalized attributes of PortableEquipment and FixEquipment entities. The equipmentNo is the primary key. The other generalized attributes are equipmentName, size, and availability. Portable equipment has type and quantity. Quantity is special in PortableEquipment entity because those equipment may have many no of items. For example the customer might need 10 microphones. FixEquipment entity has serviceDate attribute as a specialized attribute.
Customer
· The customer entity has customerNo as the primary key to identify the customers easily. And also it has name, telNo and address as it’s attributes.
PortableEquipmentHiringAgreement
· This entity has agreementNo as the primary key and also hiringCost, additinalInsurance and returnDate.
FixEquipmentHiringAgreement .
· This entity also has agreementNo as the primary key and also startDate, endDate and hiringCost.
Multiplicity Constraints
AllocatedTo
· The description says a member of staff can be allocated to more than one studio. And a branch must have at least one staff member if not the studio won’t be able to do any work.
Manages
The description says that every studio is assigned with a manager. A manager is responsible for only one studio. And of course a studio must have a manager.
Registers
· The description states that an equipment must be registered under one branch so that that branch can be responsible for that particular equipment. A one branch can have one or many equipment under them.
Keeps
· It is important that a driver keeps a record. He might transport equipment or some other item . So he might not have a record. If a driver is transporting an equipment only he can keep the record of it.
Services
· There might be situations that a one equipment is serviced by more than one engineer. The description says that each engineer must have at least one equipment to look after. And maybe more than one. So engineer is responsible for at least one equipment.
Oversees
· It says in the description that every customer will be helped by at least one engineer A engineer must oversee at least one customer according to the description.
ExamsTheRquest
· Although the description says that one request will be examined by one engineer, for the lifetime of that particular equipment there might be many requests that will be examined by many engineers.
RecordedIn
· The description says that when an equipment is transported it must be recorded. One equipment will be transported many times. So it will be recorded in many records. Sometimes an equipment may not be transported because it’s not hired. One transportation record can have more than one equipment.
RequestsFrom
· The customer will not have to make a request if he doesn’t want to.
Signs
· Customer can refrain from signing the agreement. He can sign as many agreements he likes. And also an agreement for a one equipment can be signed by only one customer.
EnablesTheCustomerToHire
· One Agreement lets the customer to hire exactly one equipment.
The following is the Relational Diagram
Following is are the reasons for selections while drawing the diagram.
·
works in relationship
This has a many to many binary relationship. To represent this relationship in a logical diagram I created a relation. There any attributes of that relationship were included. I posted The primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes. The Studio_Staff relation has 1 or many attributes . So it is a valid relation.
Staff(staffId,staffName, staffPosition, staffHireDate, staffSal, staffEmail, staffMobNo)
Studio(studioCode,studioName, studioAddress, studioPCode, studioTelNo)
Staff_Studio(*staffId,*studioCode,workStartDate)
· Staff superclass , subclass relations(optional,OR)
Here I created a table for superclass and 3 tables for subclasses separately. I placed the primary key of the superclass as a foreign key of subclasses which are primary keys in them.All the subclasses have atlaest one unique attribute. But here manager is not represented as a result of another relationship.That is manages relationship.
Staff(staffId,staffName, staffPosition, staffHireDate, staffSal, staffEmail, staffMobNo)
Driver(*staffId, lisceneNb)
Engineer(*staffId,mainRole,mainSpecialism, certification)
· manages relationship
This is a binary relationship of one to one with both sides mandatory. Here I combined the two entities and chose a one’s primary key to be the new relation’s primary key, while the other’s primary key being alternate key.
Staff_Studio (studioCode,studioName, studioAddress, studioPCode, studioTelNo,staffId,bonus)
· is allocated relationship
This is a one to one binary relationship with both sides optional. Here I selected the more mandatory and more optional side.Then placed the mandatory side’s primary key in the optional side as a foreign key.Both relations have more than one attribute and more than one relation occurrence.
Driver(*staffId,lisceneNb)
Van(vanRegNb, make, series,loadCapacity, *staffId)
· Equipment superclass ,subclass relations(mandatory,or)
Created relations for each sub entity. Placed the primary key of super entity with the rest of the attributes of sub entities. Both have unique attrbutes.
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition, equipStatus, lastServicingDate,size)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,temperature)
· concerns relationship
This is a one to many binary relationship. First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Maintenance relation is linked with both fixed and portable equipment relations.
Maintenance(maintainRefNo,maintainStartDate,maintainEndDate,maintainDescrip, *fixEquipCode,*portEquipCode)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition, equipStatus,lastServicingDate,size)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,temperature)
· is registered on relationship
This is a one to many binary relationship. First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key.
Studio(studioCode,studioName, studioAddress, studioPCode, studioTelNo)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size, *studioCode)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature ,*studioCode)
· undertakes relationship
This also has a many to many binary relationship. To represent this relationship in a logical diagram a relation was created. There any attributes of that relationship were included. Then I posted primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes.The Engineer_Maintenance relation has more than one attribute and more than one occurrence. So it’s a valid relation.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Maintenance(maintainRefNo,maintainStartDate,maintainEndDate,maintainDescrip)
Engineer_Maintenance(*staffId,*maintainRefNo,maintenanceYear)
· is responsible for relationship
This is also a one to many binary relationship. . First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key.Engineer relation is linked with fixed and portable equipment relations.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size,*staffId)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature ,*staffId)
· transports relationship
This again has a many to many binary relationship. To represent this relationship in a logical diagram a relation was created. There any attributes of that relationship were included. Then I posted primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes.All the relations have more than one attribute and ocurrences. So both all valid relations.
Driver(*staffId,lisceneNb)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature)
Driver_Of_Portable_And_Fix_Equipment(*staffId,*equipCode,transportDate)
· oversees relationship
This is also a one to many binary relationship. ISelected one side as the parent and many side as the child entity.Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills, *staffId)
· signs for relationship
This is also a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Portable_Hire(hireAgreemtNb,hireStartDate,hireEndDate,initialHireCost, insurance,totalHireCost, temperature ,*custId)
· is for relationship between Portable_Equipment and Portable_Hire
This is also a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, , temperature)
Portable_Hire(hireAgreemtNb,hireStartDate,hireEndDate,initialHireCost, insurance,totalHireCost, *portEquipCode)
· places relationship
This is also a one to many binary relationship. I selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Request(requestRefNb,requestStartDate,requestEndDate,expertiseLevel,projDescription, *custId)
· is for relationship between Portable_Equipment,Fixed_Equipment and Request
This also has a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Here I had only one request relation for both fix and portable. The reason is although having a one request relation waste some space it doesn’t affect the performance. If we have two request relaions it will affect the performance.
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size)
Request(requestRefNb,requestStartDate,requestEndDate,expertiseLevel,projDescription, *portEquipCode, *fixEquipCode)
· Ternary relationship
Here it was done like the many to many relationship. The only difference was that relationship table had0..* and parent entities will have 1..1 relationships.
Rent(*staffId,*custId,*equipCode,rentStartDate,rentEndDate,fixedRentCost)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size)
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Engineer(*staffId,mainRole,mainSpecialism, certification)
The following is the Relational Diagram
·
works in relationship
This has a many to many binary relationship. To represent this relationship in a logical diagram I created a relation. There any attributes of that relationship were included. I posted The primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes. The Studio_Staff relation has 1 or many attributes . So it is a valid relation.
Staff(staffId,staffName, staffPosition, staffHireDate, staffSal, staffEmail, staffMobNo)
Studio(studioCode,studioName, studioAddress, studioPCode, studioTelNo)
Staff_Studio(*staffId,*studioCode,workStartDate)
· Staff superclass , subclass relations(optional,OR)
Here I created a table for superclass and 3 tables for subclasses separately. I placed the primary key of the superclass as a foreign key of subclasses which are primary keys in them.All the subclasses have atlaest one unique attribute. But here manager is not represented as a result of another relationship.That is manages relationship.
Staff(staffId,staffName, staffPosition, staffHireDate, staffSal, staffEmail, staffMobNo)
Driver(*staffId, lisceneNb)
Engineer(*staffId,mainRole,mainSpecialism, certification)
· manages relationship
This is a binary relationship of one to one with both sides mandatory. Here I combined the two entities and chose a one’s primary key to be the new relation’s primary key, while the other’s primary key being alternate key.
Staff_Studio (studioCode,studioName, studioAddress, studioPCode, studioTelNo,staffId,bonus)
· is allocated relationship
This is a one to one binary relationship with both sides optional. Here I selected the more mandatory and more optional side.Then placed the mandatory side’s primary key in the optional side as a foreign key.Both relations have more than one attribute and more than one relation occurrence.
Driver(*staffId,lisceneNb)
Van(vanRegNb, make, series,loadCapacity, *staffId)
· Equipment superclass ,subclass relations(mandatory,or)
Created relations for each sub entity. Placed the primary key of super entity with the rest of the attributes of sub entities. Both have unique attrbutes.
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition, equipStatus, lastServicingDate,size)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,temperature)
· concerns relationship
This is a one to many binary relationship. First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Maintenance relation is linked with both fixed and portable equipment relations.
Maintenance(maintainRefNo,maintainStartDate,maintainEndDate,maintainDescrip, *fixEquipCode,*portEquipCode)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition, equipStatus,lastServicingDate,size)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,temperature)
· is registered on relationship
This is a one to many binary relationship. First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key.
Studio(studioCode,studioName, studioAddress, studioPCode, studioTelNo)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size, *studioCode)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature ,*studioCode)
· undertakes relationship
This also has a many to many binary relationship. To represent this relationship in a logical diagram a relation was created. There any attributes of that relationship were included. Then I posted primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes.The Engineer_Maintenance relation has more than one attribute and more than one occurrence. So it’s a valid relation.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Maintenance(maintainRefNo,maintainStartDate,maintainEndDate,maintainDescrip)
Engineer_Maintenance(*staffId,*maintainRefNo,maintenanceYear)
· is responsible for relationship
This is also a one to many binary relationship. . First I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key.Engineer relation is linked with fixed and portable equipment relations.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size,*staffId)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature ,*staffId)
· transports relationship
This again has a many to many binary relationship. To represent this relationship in a logical diagram a relation was created. There any attributes of that relationship were included. Then I posted primary keys of the entities that participate in the relationship which will act as foreign keys. These will form the primary key of the new relation along with the attributes.All the relations have more than one attribute and ocurrences. So both all valid relations.
Driver(*staffId,lisceneNb)
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature)
Driver_Of_Portable_And_Fix_Equipment(*staffId,*equipCode,transportDate)
· oversees relationship
This is also a one to many binary relationship. ISelected one side as the parent and many side as the child entity.Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Engineer(*staffId,mainRole,mainSpecialism, certification)
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills, *staffId)
· signs for relationship
This is also a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Portable_Hire(hireAgreemtNb,hireStartDate,hireEndDate,initialHireCost, insurance,totalHireCost, temperature ,*custId)
· is for relationship between Portable_Equipment and Portable_Hire
This is also a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, , temperature)
Portable_Hire(hireAgreemtNb,hireStartDate,hireEndDate,initialHireCost, insurance,totalHireCost, *portEquipCode)
· places relationship
This is also a one to many binary relationship. I selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Both the relations have more than one attribute and ocurrences. So both are valid relations.
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Request(requestRefNb,requestStartDate,requestEndDate,expertiseLevel,projDescription, *custId)
· is for relationship between Portable_Equipment,Fixed_Equipment and Request
This also has a one to many binary relationship. I Selected one side as the parent and many side as the child entity. Then Posted a copy of the primary key of parent in the child entity as a foreign key. Here I had only one request relation for both fix and portable. The reason is although having a one request relation waste some space it doesn’t affect the performance. If we have two request relaions it will affect the performance.
Portable_Equipment(portEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate, temperature)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size)
Request(requestRefNb,requestStartDate,requestEndDate,expertiseLevel,projDescription, *portEquipCode, *fixEquipCode)
· Ternary relationship
Here it was done like the many to many relationship. The only difference was that relationship table had0..* and parent entities will have 1..1 relationships.
Rent(*staffId,*custId,*equipCode,rentStartDate,rentEndDate,fixedRentCost)
Fixed_Equipment(fixEquipCode,equipMake,equipSeries,equipModel,equipCondition,equipStatus,lastServicingDate,size)
Customer(custId,custName,custAddress,custPostCode,custEmail,custSkills)
Engineer(*staffId,mainRole,mainSpecialism, certification)
No comments:
Post a Comment