Advanced Database Systems

Pages: Showing 2 - 5 of 23

Solutions
Preview

Preview:

"2. Relational Table Derived from ER Diagram :Table Name StudentNo Column Name PK FK M Data Type1 RegNo PY VARCHAR (50) 2 StuNameVARCHAR (50) 3 Contact_NoVARCHAR (15) 4 Street_addressVARCHAR (50)5 CityVARCHAR (30) 6 PostCodeVARCHAR (10) 7 StateVARCHA..

Preview Container:

"2. Relational Table Derived from ER Diagram :Table Name StudentNo Column Name PK FK M Data Type1 RegNo PY VARCHAR (50) 2 StuNameVARCHAR (50) 3 Contact_NoVARCHAR (15) 4 Street_addressVARCHAR (50)5 CityVARCHAR (30) 6 PostCodeVARCHAR (10) 7 StateVARCHAR (20) 8 CountryVARCHAR (50) 9 DtOfBirthDate10 GenderVARCHAR (10) 11 CategoryIDF Y VARCHAR (50) 12 NationalityVARCHAR (50) 13 SpecialNeedVARCHAR (200) 14 CurrentStatusVARCHAR (10) 15 Program_NumF Y VARCHAR (50)16 Kin_NameVARCHAR (50) 17 Kin_RelationVARCHAR (50) 18 Kin_ContactNoVARCHAR (15) 19 Kin_StreetAddressVARCHAR (50) 20 Kin_CityVARCHAR (30) 21 Kin_PostCodeVARCHAR (10) 22 Kin_StateVARCHAR (20) 23 Kin_CountryVARCHAR (50) 24 AdvisorIDF Y VARCHAR (50) Primary Key:Primary Key NameState Column Name SortOrderStudent_PK PK RegNo ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameStudent_Category_FK Category Y Y Category_IDStudent_Stu_Advisor_FK Stu_Advisor Y Y AdvisorIDStudent_Univ-Program_FK Univ-Program Y Y Program_Num Foreign Keys (referred from):Name Referred From Mandatory Transferable Column NameStudent_Accomodation_Student_FK Student_Accomodation Y Y RegNoStudent_Invoice_Student_FK Student_Invoice Y Y RegNoStudent_Reminder_Student_FK Student_Reminder Y Y RegNoTable Name CategoryNo Column Name PK FK M Data Type1 Category_ID PY VARCHAR (50)2 Category_NameVARCHAR (50) Primary Key :Primary Key Name State Column Name SortOrderCategory_PK PK Category_ID ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameStudent_Category_FK Student Y Y Category_IDTable Name Flat_DetailsNo Column Name PK FK M Data Type1 Place_No PY VARCHAR (50) 2 Room_No PY VARCHAR (10) 3 Flat_NoF Y VARCHAR (10) 4 Monthly_RentNUMERIC (7,2)Primary Key:Primary Key Name State Column Name Sort OrderPlace_No ASCFlat_Details_PK PKRoom_No ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameFlat_Details_Univ_Stu_Flats_FK Univ_Stu_Flats Y Y Flat_NoTable Name Flat_InspectionNo Column Name PK FK M Data Type1 Inspection_No PY VARCHAR (50)2 Inspection_DateDate3 StaffNoF Y VARCHAR (50)4 StaffNameVARCHAR (50)5 Flat_NoF Y VARCHAR (10)6 Satisfactory_CondBIT (1)7 CommentsVARCHAR (200) Primary Key:Primary Key Name State Column Name Sort OrderFlat_Inspection_PK PK Inspection_No ASCForeign Keys (referring to) :Name Refering To Mandatory Transferable Column NameFlat_Inspection_Univ_Stu_Flats_FK Univ_Stu_Flats Y Y Flat_NoFlat_Inspection_Staff_FK Staff Y Y StaffNo Table Name Hall_DetailsNo Column Name PK FK M Data Type1 Place_No PY VARCHAR (50) 2 Room_No PY VARCHAR (10) 3 Hall_NameF Y VARCHAR (50) 4 Monthly_RentNUMERIC (7,2) Primary Key:Primary Key Name State Column Name Sort OrderPlace_No ASCHall_Details_PK PKRoom_No ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameHall_Details_Univ_Hall_Residence_FK Univ_Hall_Residence Y Y Hall_NameTable Name StaffNo Column Name PK FK M Data Type1 StaffNo PY VARCHAR (50) 2 StaffNameVARCHAR (50) 3 Street_AddressVARCHAR (50) 4 CityVARCHAR (30)5 PostCodeVARCHAR (10) 6 DtOfBirthDate7 GenderVARCHAR (10) 8 PositionVARCHAR (50) 9 LocationVARCHAR (50)Primary Key:Primary Key Name State Column Name Sort OrderStaff_PK PK StaffNo ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameFlat_Inspection_Staff_FK Flat_Inspection Y Y StaffNo Table Name Stu_AdvisorNo Column Name PK FK M Data Type1 AdvisorID PY VARCHAR (50) 2 Advisor_NameVARCHAR (50) 3 PositionVARCHAR (50) 4 Deptt_NameVARCHAR (50)5 Phone_NoVARCHAR (15) 6 Room_NoVARCHAR (10)Primary Key:Primary Key Name State Column Name Sort OrderStu_Advisor_PK PK AdvisorID ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameStudent_Stu_Advisor_FK Student Y Y AdvisorIDTable Name Student_AccomodationNo Column Name PK FK M Data Type1 Lease_No PY VARCHAR (50) 2 RegNoF Y VARCHAR (50) 3 StuNameVARCHAR (50) 4 AccomodationTypeVARCHAR (10)5 Place_No Y VARCHAR6 Room_No Y VARCHAR7 AddressVARCHAR (100) 8 StartDateDate9 EndDateDatePrimary Key:Primary Key Name State Column Name Sort OrderStudent_Accomodation_PK PK Lease_No ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameStudent_Accomodation_Student_FK Student Y Y RegNoForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameStuInvoiceStuAccomoFK Student_Invoice Y Y Lease_NoTable Name Student_InvoiceNo Column Name PK FK M Data Type1 Invoice_No PY VARCHAR (50) 2 Invoice_DateDate3 Lease_NoF Y VARCHAR (50) 4 SemesterInteger5 RegNoF Y VARCHAR (50) 6 StuNameVARCHAR (50) 7 Place_No Y VARCHAR8 Room_No Y VARCHAR9 AddressVARCHAR (100) 10 PaymentDueNUMERIC (7,2) Primary Key:Primary Key Name State Column Name SortOrderStudent_Invoice_PK PK Invoice_No ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameStuInvoiceStuAccomoFK Student_Accomodation Y Y Lease_NoStudent_Invoice_Student_FK Student Y Y RegNo Foreign Keys (referred from):Name Referred From Mandatory Transferable Column NameStudent_Payment_Student_Invoice_FK Student_Payment Y Y Invoice_NoStudent_Reminder_Student_Invoice_FK Student_Reminder Y Y Invoice_NoTable Name Student_PaymentNo Column Name PK FK M Data Type1 Payment_No PY VARCHAR (50) 2 Payment_DateDate3 Invoice_NoF Y VARCHAR (50) 4 Payment_MethodVARCHAR (50) 5 Amount_PaidNUMERIC (7,2) Primary Key:Primary Key Name State Column Name Sort OrderStudent_Payment_PK PK Payment_No ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameStudent_Payment_Student_Invoice_FK Student_Invoice Y Y Invoice_NoTable Name Student_ReminderNo Column Name PK FK M Data Type1 ReminderID PY VARCHAR (50) 2 Reminder_DateDate3 Invoice_NoF Y VARCHAR (50) 4 RegNoF Y VARCHAR (50) 5 StuNameVARCHAR (50)Primary Key:Primary Key Name State Column Name Sort OrderStudent_Reminder_PK PK ReminderID ASCForeign Keys (referring to):Name Refering To Mandatory Transferable Column NameStudent_Reminder_Student_FK Student Y Y RegNoStudent_Reminder_Student_Invoice_FK Student_Invoice Y Y Invoice_No Table Name Univ-ProgramNo Column Name PK FK M Data Type1 Program_Num PY VARCHAR (50)2 Program_NameVARCHAR (100)3 Program_LeaderVARCHAR (50)4 Phone_NoVARCHAR (15)5 Room_NoVARCHAR (10)6 Deptt_NameVARCHAR (50) Primary Key:Primary Key Name State Column Name Sort OrderUniv-Program_PK PK Program_Num ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameStudent_Univ-Program_FK Student Y Y Program_NumTable Name Univ_Hall_ResidenceNo Column Name PK FK M Data Type1 Hall_Name PY VARCHAR (50) 2 AddressVARCHAR (100) 3 Phone_NoVARCHAR (15) 4 Hall_SupervisorVARCHAR (50)Primary Key:Primary Key Name State Column Name Sort OrderUniv_Hall_Residence_PK PK Hall_Name ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameHall_Details_Univ_Hall_Residence_FK Hall_Details Y Y Hall_Name Table Name Univ_Stu_FlatsNo Column Name PK FK M Data Type1 Flat_No PY VARCHAR (10) 2 AddressVARCHAR (100) 3 No_Of_BedroomsInteger Primary Key:Primary Key Name State Column Name Sort OrderUniv_Stu_Flats_PK PK Flat_No ASCForeign Keys (referred from):Name Referred From Mandatory Transferable Column NameFlat_Details_Univ_Stu_Flats_FK Flat_Details Y Y Flat_NoFlat_Inspection_Univ_Stu_Flats_FK Flat_Inspection Y Y Flat_No "