Relational-Database-Design-final

《Relational-Database-Design-final》由会员分享，可在线阅读，更多相关《Relational-Database-Design-final（51页珍藏版）》请在装配图网上搜索。

1、Coursework Submission FormInstructions1. Please complete the form using Capital Letters.2. Coursework must be submitted through the Service counter and it is the responsibility of the student to complete the Coursework Submission Log BookName of Student:Tran Dinh Nam - 018800006357Vu Anh Long - 0188

2、00005846VuE-mail Address:Vuanhlong89Contact No:90875073Name of Lecturer:Dr.N. KrishnamoorthyStudent ID:018800005846Class Code:U08049Subject Code:U08049-Relational Database DesignAssignment No:T109No. Of pages includingthis cover page: 47OtherSpecifications: CD 1 enclosedSubmission Date:3/4/2009Due D

3、ate:3/4/2009Declaration:Declare that this assignment is my original work and that I have acknowledged any use of published or unpublished works of other people. I understand that I will be penalized for plagiarism and late submission.Signature:Task 1 2 3 45 TotalMarkerInternal ModeratorExternal Mode

4、ratorMarkers Comment:推荐精选Table of Contents1. Introduction31.1 Back ground31.1.1 Explanation of Database and Relational Database31.1.2 Explanation of Entity Relationship Diagrams (ERD)51.2 Analysis and design of the requirement52. Task 1: Detail entity relational diagram reflect Blue trains requireme

5、nt62.1 Clearly of assumption62.2 Real world entities162.2.1 Entities and attributes.162.2.2 Multi-valued attributes indicated182.3.3 Weak entities192.3 Super entities and sub entities202.4 Entities relationship and a complete EER diagram213. Task 2: Development of Relational Database schema223.1 Tra

6、nslation of Weak Entity233.2 Translation of multi-valued attributes243.3 Translation of 1:1 Relationship243.4 Translation of 1: N Relationship253.4.1 Manager - Employee253.4.2 Developer Course; Developer External253.4.3 Trainer - Training Session, Trainer-External263.5 Translation of N: M Relationsh

7、ip273.6 Translation of Super/Sub Entities284. Task 3: Relation by relational database software (SQL Server 2005)294.1 Create table294.2 Relational Diagram in SQL server software34推荐精选4.3 Insert data365. Query the database395.1 Explanation of Relational Algebra (RA)395.2 Explanation of Server Query L

8、anguage (SQL)395.3 Question 140Relational Algebra (RA):40Server Query Language (SQL):40Result tested:405.4 Question 2.41Relational Algebra (RA):41Server Query Language (SQL):41Result tested:416. Conclusion427. References438. Task distributes.449. Appendix459.1 Tables and figures459.2 T report46推荐精选1

9、. Introduction1.1 Back ground1.1.1 Explanation of Database and Relational DatabaseWhat is a Database? Database or DB (Short Form of Database) is a structure collection of documents or data in a Computer System so you can access and looking for information you want very fast and exactly .So if you wa

10、nt access information that you need DBMS (Data Base Management System), this is a some programs permit additional, structure and choice data in database .Storage data in database look like when you store things in a box, database provide quickly, convenient and very strong. Figure 1 - illustrates th

11、e software subsystem of database management system Database was designed so storage, management and get information. How do you contain a lot of things so easily search and management when need it for the data .Storage information by type of structure of the tables (Rows and Columns) will help you e

12、asily access when necessary .Example :The stored information of family members include(Name ,Age ,Gender ,Hobbies ,Relationship) very correctly 推荐精选,cannot be mistake.The Relational Database Model concept was first found and research by Edgar Frank Cood in IBM research report RJ599, 19/08/1969. But

13、in that time it was a technology foundation for Large Shared Data Banks .Relational Database Model is a collection of relations among entities, relationship thought of as a tables of values .When you want to designing a database you need give the best decision by real world to for your Database Syst

14、em .This included which a tables was created, what is the columns will obtain, or relationship among tables. Now the relational model is being useful and popular applications in the area of traditional data processingFigure 2 - illustrates a database design (simplified)推荐精选1.1.2 Explanation of Entit

15、y Relationship Diagrams (ERD)Entity Relationship Diagrams illustrate the logical structure of database so they have some similar point with EER. Extended Entity Relationship (EER Model) is a conceptual or semantic of data model and describing the data requirement for a new information system and eas

16、y to understand with graphic notation. EER model is a high level or conceptual data model incorporation into original use to design database .It was created and reflected for more properties and constraints that found in more complex database, including in engineering design and manufacturing (CAD,

17、CAM), Telecommunication, software system and (GIS).The EER model contain all of the modeling concepts by ER model ,however it also contain the concepts about Subclass and sup-class ,union type or category .In EER almost of abstract attributes are specified by relationship-sets among entity sets rath

18、er than local to entity-sets.EER Model not assist set valued attributes , tulle attributes ,union value classes and derived classes and attributes .So sets valued attributes can be using additional entity sets , tuples attributes can be using auxiliary relationship sets and union values class using

19、an auxiliary entity set specified . 1.2 Analysis and design of the requirement BlueTrain is an education institution offering many courses over its award winning virtual learning platform. A critical function of the human resource department of BlueTrain is to provide proper training to the employee

20、s. Each employee is identified by their employee ID. The company keeps track of other employee information such as their contact number, address and email. Each employee is assigned to a department. Each department is given a unique department name and is managed by a manager. The human resource dep

21、artment develops many training courses, each identified by the course title. The database captures other course information such as the description, learning outcomes and duration of the course. Each training course is developed by a developer who creates the training materials trainer guide, and id

22、entifies the needed resources for the course. The resource can be classified as software resource or hardware resource. The developers can be internal developers who are employees of the company, and external developers who are not employees of the company. The external developers are paid for the t

23、raining course they develop. Each course has a list of qualified trainers, and a trainer may be qualified to train on more than one training course.Each training course may be offered many times in different training sessions. Each session is conducted by a qualified trainer. Internal trainers are n

24、ot paid for the training sessions they conduct. External trainers are paid for the training session they conduct. The employees register for the training session one week before the training session starts, subject to the approval of their department head. Each employee must complete a feedback form

25、 after each training session. The human resource department keeps track of the total training hour in each fiscal year for each employee. The total training hour is derived all the training sessions the employees attended over the fiscal year.推荐精选 At First we need to specify the requirements and ana

26、lysis necessary in the design process to create models EER. Can we analysis of how many objects and attributes in the system received complete the design quickly and exactly? Specify to the number of rows, columns, data types and relationships of the table relating to the design .We need to have a w

27、hole (within) look at the actual system to obtain high productivity and effective. Making model designed to estimate the work needed and the operating system.2. Task 1: Detail entity relational diagram reflect Blue trains requirement2.1 Clearly of assumptionPersonPerson class is a very important cla

28、ss because it contain all information of Manager ,Employee ,Developer ,Trainer.(it common a humans information).In this class have some attribute :Name ,Sex ,Age ,Contact.and it has some inherit s attribute so some another class can also use it .The following some attribute in this table:PersonNoAtt

29、ributeDescription1Person_IdEvery person will have unique Person_Id , it will be easier when research persons information.2Full NamePerson will be have full name,avoid if have many the same name in company .3SexSex will be distinguish sex(female or male ).4Day Of BirthDay of birth will be necessary w

30、hen search some personal information of employees in company5AddressAddress will be necessary when search some personal information of employees in company6EmailEmail will be necessary when contact with employees in company7NoteNote is a some notes about employees(extra information)推荐精选Manager This

31、class will managed about Employees class ,all information about employees will contain in this class and manager can modify or update .In every Department have another Manager managed , Each department is given a unique department name and is managed by a manager. ManagerNoAttributeDescription1Emplo

32、yee_IdEvery employees will have unique Employee_Id , it will be easier when research employees information.2Manager_IdEvery manager will have unique Manager_Id , it will be easier when research managers information or want to know which department he managedEmployee Each employee is identified by th

33、eir employee ID. The company keeps track of other employee information such as their contact number, address and email.This class have some constraints with Manager class ,Department class and more than in system .This is a class have very complete information every employee in company.EmployeeNoAtt

34、ributeDescription1Employee_IdEvery employees will have unique Employee_Id , it will be easier when research employees information.2Person _IdEvery person will have unique Person_Id , it will be easier when research persons information.3Office_ NumberOffice number will be to specify number of office

35、because have many office in company4SalarySalary will be contain salary every months of all employees in company5PositionPosition will be identify job title every employee in company6DepartmentDepartment will be determine every department in company, because have many department in a company7NoteNot

36、e is a some notes about employee(extra information)推荐精选External_Developer Each training course is developed by a developer who creates the training materials trainer guide, and identifies the needed resources for the course. The developers can be internal developers who are employees of the company,

37、 and external developers who are not employees of the company. The external developers are paid for the training course they develop.External_DeveloperNoAttributeDescription1External_Developer_IdEvery external developer will be have unique External_Developer_Id , with purpose determined developer in

38、 company or out side2Person_IdEvery person will have unique Person_Id , it will be easier when research persons information and with external developer will have some detail such as salary feesExternal_CourseEach training course may be offered many times in different training sessions. External cour

39、se is a course was created by external developer so they will have course fees for all course created. External course will be separated with Internal course was created by developer in company.External_CourseNoAttributeDescription1External_Course_IdEvery external course will be have unique External

40、_Course_Id , with purpose determined course was developed in company or out side2External_Developer_IdEvery developer will have unique External_Developer_Id , it will be easier when research courses information and who have developed3Course_IdHave many course so Course_id will be determined every an

41、other course4Develop_FeesDevelop_Fees will be important with external developer,they will have fees for all course developed推荐精选Trainer Trainer is a people teaching training course for employees in company and learning out come. Internal trainers are not paid for the training sessions they conduct.

42、External trainers are paid for the training session they conduct. The same with internal developer and external developer however external developer will be have training fees TrainerNoAttributeDescription1Trainer_IdEvery trainer will be have unique Trainer_Id , with purpose determined trainer in co

43、mpany or out side2Person_IdEvery person will have unique Person_Id , it will be easier when research persons information3DescriptionDescription will be extra some information about trainer4IsExternalIsExternal will be identify trainer in company or hired External_Trainer They are (Trainer)not workin

44、g in company, they just training for employee and they have paid fees. They come from outside and they are not employees in a company so they are just have a bit information compared with employees.External TrainerNoAttributeDescription1External_Trainer_IdEvery trainer will be have unique Trainer_Id

45、 , with purpose determined trainer in company or out side2Person_IdEvery person will have unique Person_Id , it will be easier when research persons information3DescriptionDescription will be extra some information about trainers推荐精选Department Each employee is assigned to a department. Each departme

46、nt is given a unique department name and department id is managed by a managers. Department will be identify with many departments in company and separated all of them. Department will be showed every department with different tasks in a companyDepartmentNoAttributeDescription1Department_IdDepartmen

47、t will be determine every department in company,because have many department in a company2TitleTitle will be show the name of department.3DescriptionDescription will be extra some information about department4Manager_IdEvery manager will have unique Manager_Id , it will be easier when research manag

48、ers information or want to know which department he managedCourse The human resource department develops many training courses, each identified by the course title. The database captures other course information such as the description, learning outcomes and duration of the course. . Each course has

49、 a list of qualified trainers, and a trainer may be qualified to train on more than one training course. Each training course may be offered many times in different training sessions.CourseNoAttributeDescription1Course_IdHave many course so Course_id will be determined every another course2TitleTitl

50、e will be show the name of department.3Developer_IdEvery developer will be have unique Developer_Id , with purpose determined developer in company or out side4DescriptionDescription will be extra some information about course5DurationDuration is a about time of course6Learning_OutcomeThe result of e

51、very employees after training course.推荐精选Internal_CourseEach training course may be offered many times in different training sessions. Internal course is a course was created by internal developer in company so they will not have course fees for all course created. Internal course will be separated

52、with External course was created by developed outside.Internal_CourseNoAttributeDescription1Internal_Course_IdEvery internal course will be have unique Internal_Course_Id , with purpose determined course was developed in company 2Employee_IdEvery employees will have unique Employee_Id , it will be e

53、asier when research employees information.3Course_IdHave many course so Course_Id will be determined every another course(Course was developed by developer in company)Resourse The resource can be classified as software resource or hardware resource. The human resource department keeps track of the t

54、otal training hour in each fiscal year for each employee. The total training hour is derived all the training sessions the employees attended over the fiscal year.ResourseNoAttributeDescription1Resourse_IdHave many course so Resourse_Id will be determined every another resourse2Resourse_NameEvery re

55、sourse will have Resourse_Name , it will be easier when research resourses information.3Resourse_Type_IdResourse_Type_Id will classify every type of resourse.4DescriptionDescription will be extra some information about resourse推荐精选Trainning_Session Each training course may be offered many times in d

56、ifferent training sessions. Each session is conducted by a qualified trainer. The employees register for the training session one week before the training session starts, subject to the approval of their department headTrainning_SessionNoAttributeDescription1Trainning_Session_IdHave many training se

57、ssion so Trainning_Session_Id will be determined every another training session2Course_IdHave many course so Course_Id will be determined every another courses.3Start_TimeWill be determined duration start of training session4End_TimeWill be determined duration end of training sessionExternal_Trainni

58、ng_SessionHave two training session are external training session and internal training session. External training session is a session was created by developer from outside it means company will be paid training session fees.External_Trainning_SessionNoAttributeDescription1External_Trainning_Sessio

59、n_IdHave many training session so External_Trainnig_Session_Id will be determined every another external training session2Trainning_Session_IdEvery training session id will be have unique training sessionHave many training session so Trainning_Session_Id will be determined every another training ses

60、sion3External_Trainer_IdEvery external_Trainer_Id will be have uniqueExternal_Trainer_Id will be determined every external trainer4Trainning_FeesTrainning_Fees is a fees was paid for external training session推荐精选Internal_Trainning_SessionHave two training session are external training session and in

61、ternal training session. Internal training session is a session was created by developer in company it means company will not be paid training session fees because they are employees in company, they just have salary.Internal_Trainning_SessionNoAttributeDescription1Internal_Trainning_Session_IdHave

62、many training session so Internal_Trainnig_Session_Id will be determined every another internal training session2Trainning_Session_IdEvery training session id will be have unique internal training session.Have many training session so Trainning_Session_Id will be determined every another training session3Employee_IdEvery employees will have unique Employee_Id , it will be easier when research employees information. In this case it