Cast asdatetime and analytics principles

Database and Analytics Principles

Database and Analytics Principles
LD7084

databaseinaprofessionalmodellingtoolofyourchoicesuchasstarUMLordraw.io.Allentity

types,attributesandtherelationshipsbetweenthemmustbelabelledclearly.Yourdesignshould

Theminimumcardinalityiseitherzeroorone,thesymbolsusedare,correspondingly,acircleora bar.Similarly,themaximumcardinalitymustbeeitheroneormany,shownwiththebarorthecrow’s foot.Thecrow’sfootsymbolisanaturalindicatorformultiplicity;itreallylookslikethe“many”end.

Ofthedifferentchoicesavailablefordiagrammingcardinality,thecrow’sfootnotationis clearlythemostdesirable.Itshowsbothminimumandmaximumcardinalityinavisiblegraphic format.Themultiplicitysymbolisintuitiveandeasilyunderstoodbynon-technicalreaders.Itdoes notuseexpressionsthathavetobecarefullyparsedtointerprettheirmeaning.Itplacesthesymbols directlyontherelationshiplinesotheyremainvisibleandunambiguousinacomplexdiagram.The crow’sfootnotationshouldbeusedinallconceptualdatamodeldiagramsthataretobereviewedby businessusers.Itisalsoappropriatetouseinphysicaldatabasedesignmodelsandobject-oriented classmodels,althoughitislessacceptedinthosedisciplines.

TblTutorClassAssign (Admin) TutorClassAssignId TutorId		tblClass (Admin)		tblLearningResourcesType (Admin)
		ClassId		LearningResourcesTypeId
			ClassName	LearningresourcesType
		Starting_Date		ResourceType
		Ending_Date		duration
		MinimumClassSize		Author
		MaximumClassSize		DateOfIssue
		IsActive		IsActive
		CreatedOn		CreatedOn
	CreatedOn	FK_ClassId1238		FK_LearningResourcesTypeId1234
		tblEnrollment (Admin)					tblLearningActivity (Admin)
							LearningActivityId
		EnrollmentId			FeeType		LearningResourcesTypeId
		FeeId			Fee		LearningActivity
		ClassId
							duration
		LearnerId
							Author
		CampusId
		duration		DateOfIssue
		Fee		FK_LearnerId12349		tblLearner (Admin)
		PhoneNumber		LearnerId				IsActive
		Address		LastName				CreatedOn
		IsActive		First_Name
		CreatedOn		Email
		Deduction		Age
		tblCampus (Admin)		Phone_Number
		CampusId		IsActive
		FullTimeSemesterYearly		CreatedOn

MySQLrelationaldatabase.Yourtablesshouldshowallconstraintsappliedateitherthecolumnor

database.Almosteveryfunctionsuchasretrievingdatafromthedatabase,creatinganewdatabase, manipulatingdataanddatabasessuchasinsertion,deletionandupdatingcanbeperformedusing SQL.Itisauser-friendlyanddomain-specificlanguage.

Portable:SQLrunsonmainframes,PCs,laptops,servers,tabletsandsmartphones.Itrunsinlocal systems,intranetandinternet.DatabasesusingSQLcanbemovedfromdevicetoanotherwithout problems.

Programminglanguage:SQLcanbeusedbyprogrammerswritingapplicationsusingdatabases,for example,shoppingapplicationsontheinternet.

Completelanguageforadatabase:SQLisusedtocreatedatabases,managesecurityofadatabase. Whileit’smainuseisforupdating,retrievingandsharingdatawithmultipleusers.

)WITH (PAD_INDEX=OFF)ON [PRIMARY]
)ON [PRIMARY]

INSERT [Admin].[Learner]([LearnerId], [LastName], [First_Name], [Email], [Age], [Gender], [LearningStyle], [Phone_Number], [IsActive], [CreatedOn])VALUES (1, N'John',N'Em',N'John@gmail.com', 30,N'Male',N'Fulltime',N'45789652', 1, CAST(N'2021-12-24 15:24:16.157'ASDateTime)),
(2,N'James',N'Mary',N'James@mail.com', 25,N'Male',N'Parttime',N'78754589', 1,CAST(N'2021-12-24 15:25:35.733'ASDateTime)),
(3,N'Jennifer',N'Lopez',N'Jennifer@email.com', 35,N'Female',N'Fulltime', N'78754214', 1,CAST(N'2021-12-24 15:26:42.390'ASDateTime))
,
(4,N'Robert',N'Patricia',N'Robert@email.com', 28,N'Male',N'Fulltime', N'78454578', 1,CAST(N'2021-12-24 15:30:10.357'ASDateTime)),
(5,N'David',N'Barbara',N'David@email.com', 27,N'Male',N'parttime',
N'78454874', 1,CAST(N'2021-12-24 15:31:10.050'ASDateTime))

Class:

,(3,N'C Level',CAST(N'2022-01-01 00:00:00.000'ASDateTime),CAST(N'2022-12-31 00:00:00.000'ASDateTime), 20, 0, 1,CAST(N'2021-12-24 15:46:17.180'AS
DateTime)),
(4,N'D Level',CAST(N'2022-01-01 00:00:00.000'ASDateTime),CAST(N'2022-12-31 00:00:00.000'ASDateTime), 12, 0, 1,CAST(N'2021-12-24 15:46:33.940'AS
DateTime)),
(5,N'E Level',CAST(N'2022-01-01 00:00:00.000'ASDateTime),CAST(N'2022-12-31 00:00:00.000'ASDateTime), 5, 0, 1,CAST(N'2021-12-24 15:46:52.633'AS
DateTime))

Enrollment:

CREATETABLE [Admin].[FeeType](
[FeeId] [bigint] DENTITY(1,1)NOTNULL, [FeeType] [nvarchar](100)NOTNULL, [Fee] [nvarchar](100)NOTNULL,

Database and Analytics Principles

Data nsertion

INSERT [Admin].[FeeType]([FeeId], [FeeType], [Fee], [IsActive], [CreatedOn]) VALUES (1,N'Full Time',N'300', 1,CAST(N'2021-12-24 17:40:19.450'ASDateTime)) ,(2,N'Part Time',N'150', 1,CAST(N'2021-12-24 17:40:29.280'ASDateTime))

)WITH (PAD_INDEX = OFF)ON [PRIMARY]
)ON [PRIMARY]

Learning Activity :

Data nsertion

INSERT [Admin].[LearningActivity]([LearningActivityId],
[LearningResourcesTypeId], [LearningActivity], [duration], [Author],
[DateOfIssue], [Marks], [IsActive], [CreatedOn])VALUES (9, 3,N'fill n the

CREATETABLE [Admin].[Tutor](
[TutorId] [bigint] DENTITY(1,1)NOTNULL,
[LastName] [nvarchar](500)NOTNULL,
[First_Name] [nvarchar](500)NOTNULL,
[Email] [nvarchar](100)NOTNULL,
[Salary] [bigint] NOTNULL,
[Gender] [nvarchar](100)NOTNULL,
[Address] [nvarchar](2000)NOTNULL,
[Qualifications] [nvarchar](2000)NOTNULL,
[Type] [nvarchar](100)NOTNULL,
[PhoneNumber] [nvarchar](100)NOTNULL,
[IsActive] [int] NOTNULL,
[CreatedOn] [datetime] NOTNULL,
CONSTRAINT [PK__Tutor__77C70FE2F58B610F] PRIMARYKEYCLUSTERED

INSERT [Admin].[Tutor]([TutorId], [LastName], [First_Name], [Email], [Salary], [Gender], [Address], [Qualifications], [Type], [PhoneNumber], [IsActive], [CreatedOn])VALUES (1,N'Richard',N'Al',N'Richard@Email.com', 50000,N'Male', N'abcd',N'Master',N'Fulltime',N'45487245', 1,CAST(N'2021-12-24 15:35:18.340' ASDateTime))
,
(2,N'Susan',N'I',N'Susan@email.com', 50000,N'Female',N'asdasd',N'Master', N'PartTime',N'78787878', 1,CAST(N'2021-12-24 15:36:08.737'ASDateTime)) ,
(3,N'Joseph',N'B',N'Joseph@gmail.com', 50000,N'Male',N'fghdgfhgf',
N'Master',N'FullTime',N'98562325', 1,CAST(N'2021-12-24 15:40:01.330'AS DateTime)),
(4,N'Jessica',N'Re',N'Jessica@Email.com', 50000,N'Female',N'pokfdaskd', N'Master',N'PArtTime',N'878454', 1,CAST(N'2021-12-24 15:40:38.033'AS DateTime)),
(5,N'Thomas',N'Bal',N'Thomas@gmail.com', 5000,N'Male',N'aksdashd',
N'Master',N'FullTime',N'8723232', 1,CAST(N'2021-12-24 15:41:18.033'AS DateTime))

Tutor Class Assignment:

following:a.Astatementinvolvingaself-join.b.Astatementinvolvinganequi-join.c.A

statementinvolvingatleastonegroupfunctiond.Astatementinvolvingatleastone

SELECTdistinct*FROMADMIN.Enrollment t1,ADMIN.Enrollment t2
where t1.LearnerID = t2.LearnerID and t2.deduction <> 0 and t1.deduction <> 0

SELECT LearnerId,SUM(cast(Fee asnt))as TotalFee FROMADMIN.Enrollment GROUPBY LearnerId

SELECT DateOfIssue,*FROMADMIN.LearningActivity

Database and Analytics Principles

recruitments across the different countries n the world.

Thefourkeydecisionsmadeduringthedesignofadimensionalmodelinclude:

Inthefollowingdemonstration,learnerisadimension,thedatabasestorethelearnerid,lastname, firstname,email,age,gender,learningstyle(Fulltimeorparttime)andphonenumber.Tutorisa dimension,thestoretheirname,salary,type,emailaddressandqualifications.Classisadimension hasastartingdate,endingdateandsize,minimumclassmaximumsizeCampusisadimension.Fees areadimension.Learningresourcesisadimensionincludingvideos,audios,andPowerPoint presentationsandreadingtext.Eachlearningresourcehasrelevantdatasuchasauniqueid,type, duration,author,dateofissueetc.Eachlearningresourcewillbeconnectedwithasetoflearning activitiessuchasdraganddrop,fillinthemissing,writefullanswers,sortorderofwordsorsentences etc.Eachactivityshouldhaveanid,issuedate,author,marksetc.Enrollmentisameasure,itstores enrollmentdatasuchaslearnerid,courseduration,fees,learneraddressandcontactdetailsetc

ModelofStarSchema:
StarSchemaiswhenthereisadirectrelationshipbetweenthefacttableandeachdimension.Each facttablecanhaveaconnection(orrelationship)tomultipledimensionssoitwillcomeupwitha viewlikebelowdiagram.

Dataset n Appendix 1. You must use Tableau to analyze the data set provided and find answers to the

following:

Database and Analytics Principles

checkthatshouldbeincorporatedinthedatabaseistoensurethatifthereisareferencetoacertain object,thatobjectmustexist.

2. Given the tem ordered comes with 60 days warranty from the date t was ordered. Use Tableau to calculate the Warranty End date.

InaworksheetinTableau,selectAnalysis>CreateCalculatedField.IntheCalculationEditorthat opens,givethecalculatedfieldanamecalExpiryDate.UsedateaddFormulatoaddexpirydatein orderdate.

4.UsingTableau,displayallcitiesthatgeneratedmorethan$3,000,000fortheyear2019.

Createtotalsalescalculatedfield.DragtheOrderDatedimensiontoColumns.Tableauaggregates thedatebyyear2019,andcreatescolumnheaders.DragthetotalsalescalculatedfieldtoRows. TableauaggregatesSalesasSUManddisplaysasimplelinechart.DragthecitiesmeasuretoRows anddropittotherightoftheSalesmeasure.Tableaucreatesseparateaxesalongthelefmarginfor Sales.Nowfilterthechartandextractallcitiesthatgeneratedmorethan$3,000,000fortheyear 2019.

Database and Analytics Principles

measure.Tableaucreatesseparateaxesalongthelefmarginforcount.Nowdisplaythetop5selling

Createtotalsalescalculatedfield.DragtheOrderDatedimensiontoColumns.Tableauaggregates thedatebyyear2019,andcreatescolumnheaders.DragthetotalsalescalculatedfieldtoRows. TableauaggregatesSalesasSUManddisplaysasimplelinechart.DragthedatemeasuretoRowsand dropittotherightoftheSalesmeasure.TableaucreatesseparateaxesalongthelefmarginforSales. Nowfilterthechartandextractallsalesthatgeneratedmonthwisefortheyear2019.

8.Usingtableau,findthemaximumsalesgeneratedinBoston,LosAngeles,SeattleandNewYork.

DragthecalcSalesdimensiontoColumns.TableauaggregatesmaximumsalesgeneratedinBoston, LosAngeles,SeattleandNewYork.andcreatescolumnheaders.DragtheCitiesfieldtoRows.